<!DOCTYPE html>
<!-- saved from url=(0051)https://www.cnblogs.com/jmx-bigdata/p/13708834.html -->
<html lang="zh-cn"><head><meta http-equiv="Content-Type" content="text/html; charset=UTF-8">
    
    <meta name="viewport" content="width=device-width, initial-scale=1.0">
    <meta name="referrer" content="never">
    <meta name="description" content="在上一篇分享中，我们介绍了ClickHouse的安装部署和简单使用。本文将介绍ClickHouse中一个非常重要的概念—表引擎(ta...">
    <meta property="og:description" content="在上一篇分享中，我们介绍了ClickHouse的安装部署和简单使用。本文将介绍ClickHouse中一个非常重要的概念—表引擎(ta...">
    <meta http-equiv="Cache-Control" content="no-transform">
    <meta http-equiv="Cache-Control" content="no-siteapp">
    <meta http-equiv="X-UA-Compatible" content="IE=edge">
    <title>篇二|什么是ClickHouse的表引擎? - 大数据技术与数仓 - 博客园</title>
    <link id="favicon" rel="shortcut icon" href="https://common.cnblogs.com/favicon.svg" type="image/svg+xml">
    
    <link rel="stylesheet" href="./篇二_什么是ClickHouse的表引擎_ - 大数据技术与数仓 - 博客园_files/blog-common.min.css">
    <link id="MainCss" rel="stylesheet" href="./篇二_什么是ClickHouse的表引擎_ - 大数据技术与数仓 - 博客园_files/bundle-codinglife.min.css">
    
    <link id="mobile-style" media="only screen and (max-width: 767px)" type="text/css" rel="stylesheet" href="./篇二_什么是ClickHouse的表引擎_ - 大数据技术与数仓 - 博客园_files/bundle-codinglife-mobile.min.css">
    
    <link type="application/rss+xml" rel="alternate" href="https://www.cnblogs.com/jmx-bigdata/rss">
    <link type="application/rsd+xml" rel="EditURI" href="https://www.cnblogs.com/jmx-bigdata/rsd.xml">
    <link type="application/wlwmanifest+xml" rel="wlwmanifest" href="https://www.cnblogs.com/jmx-bigdata/wlwmanifest.xml">
    <script src="./篇二_什么是ClickHouse的表引擎_ - 大数据技术与数仓 - 博客园_files/osd.js.下载"></script><script async="" src="./篇二_什么是ClickHouse的表引擎_ - 大数据技术与数仓 - 博客园_files/analytics.js.下载"></script><script>
        var currentBlogId = 622219;
        var currentBlogApp = 'jmx-bigdata';
        var cb_enable_mathjax = false;
        var isLogined = false;
        var isBlogOwner = false;
        var skinName = 'CodingLife';
        var visitorUserId = '';
    </script>
        <script>
            var currentPostDateAdded = '2020-09-21 09:38';
        </script>
    <script src="./篇二_什么是ClickHouse的表引擎_ - 大数据技术与数仓 - 博客园_files/jquery-2.2.0.min.js.下载"></script>
    <script src="./篇二_什么是ClickHouse的表引擎_ - 大数据技术与数仓 - 博客园_files/blog-common.min.js.下载"></script><style type="text/css">.medium-zoom-overlay{position:fixed;top:0;right:0;bottom:0;left:0;opacity:0;transition:opacity .3s;will-change:opacity}.medium-zoom--opened .medium-zoom-overlay{cursor:pointer;cursor:zoom-out;opacity:1}.medium-zoom-image{cursor:pointer;cursor:zoom-in;transition:transform .3s cubic-bezier(.2,0,.2,1)!important}.medium-zoom-image--hidden{visibility:hidden}.medium-zoom-image--opened{position:relative;cursor:pointer;cursor:zoom-out;will-change:transform}</style>
    
    
    
<link rel="stylesheet" type="text/css" href="./篇二_什么是ClickHouse的表引擎_ - 大数据技术与数仓 - 博客园_files/prism-tomorrow-custom.css"><meta http-equiv="origin-trial" content="A+b/H0b8RPXNaJgaNFpO0YOFuGK6myDQXlwnJB3SwzvNMfcndat4DZYMrP4ClJIzYWo3/yP2S+8FTZ/lpqbPAAEAAABueyJvcmlnaW4iOiJodHRwczovL2ltYXNkay5nb29nbGVhcGlzLmNvbTo0NDMiLCJmZWF0dXJlIjoiVHJ1c3RUb2tlbnMiLCJleHBpcnkiOjE2MjYyMjA3OTksImlzVGhpcmRQYXJ0eSI6dHJ1ZX0="><meta http-equiv="origin-trial" content="A9ZgbRtm4pU3oZiuNzOsKcC8ppFSZdcjP2qYcdQrFKVzkmiWH1kdYY1Mi9x7G8+PS8HV9Ha9Cz0gaMdKsiVZIgMAAAB7eyJvcmlnaW4iOiJodHRwczovL2RvdWJsZWNsaWNrLm5ldDo0NDMiLCJmZWF0dXJlIjoiVHJ1c3RUb2tlbnMiLCJleHBpcnkiOjE2MjYyMjA3OTksImlzU3ViZG9tYWluIjp0cnVlLCJpc1RoaXJkUGFydHkiOnRydWV9"><meta http-equiv="origin-trial" content="AxL6oBxcpn5rQDPKSAs+d0oxNyJYq2/4esBUh3Yx5z8QfcLu+AU8iFCXYRcr/CEEfDnkxxLTsvXPJFQBxHfvkgMAAACBeyJvcmlnaW4iOiJodHRwczovL2dvb2dsZXRhZ3NlcnZpY2VzLmNvbTo0NDMiLCJmZWF0dXJlIjoiVHJ1c3RUb2tlbnMiLCJleHBpcnkiOjE2MjYyMjA3OTksImlzU3ViZG9tYWluIjp0cnVlLCJpc1RoaXJkUGFydHkiOnRydWV9"><meta http-equiv="origin-trial" content="A9KPtG5kl3oLTk21xqynDPGQ5t18bSOpwt0w6kGa6dEWbuwjpffmdUpR3W+faZDubGT+KIk2do0BX2ca16x8qAcAAACBeyJvcmlnaW4iOiJodHRwczovL2dvb2dsZXN5bmRpY2F0aW9uLmNvbTo0NDMiLCJmZWF0dXJlIjoiVHJ1c3RUb2tlbnMiLCJleHBpcnkiOjE2MjYyMjA3OTksImlzU3ViZG9tYWluIjp0cnVlLCJpc1RoaXJkUGFydHkiOnRydWV9"><meta http-equiv="origin-trial" content="AookgM0K6zABiuRTZwpn+R95G2CKmUH/2+zf2kS/QpMlVZ6HTI6QekeLkrJyxeIi62p2ejcQTF464pkdlx0Nwg0AAABmeyJvcmlnaW4iOiJodHRwczovL3d3dy5nb29nbGUuY29tOjQ0MyIsImZlYXR1cmUiOiJUcnVzdFRva2VucyIsImV4cGlyeSI6MTYzNDA4MzE5OSwiaXNTdWJkb21haW4iOnRydWV9"><meta http-equiv="origin-trial" content="A3HucHUo1oW9s+9kIKz8mLkbcmdaj5lxt3eiIMp1Nh49dkkBlg1Fhg4Fd/r0vL69mRRA36YutI9P/lJUfL8csQoAAACFeyJvcmlnaW4iOiJodHRwczovL2RvdWJsZWNsaWNrLm5ldDo0NDMiLCJmZWF0dXJlIjoiQ29udmVyc2lvbk1lYXN1cmVtZW50IiwiZXhwaXJ5IjoxNjI2MjIwNzk5LCJpc1N1YmRvbWFpbiI6dHJ1ZSwiaXNUaGlyZFBhcnR5Ijp0cnVlfQ=="><meta http-equiv="origin-trial" content="A0OysezhLoCRYomumeYlubLurZTCmsjTb087OvtCy95jNM65cfEsbajrJnhaGwiTxhz38ZZbm+UhUwQuXfVPTg0AAACLeyJvcmlnaW4iOiJodHRwczovL2dvb2dsZXN5bmRpY2F0aW9uLmNvbTo0NDMiLCJmZWF0dXJlIjoiQ29udmVyc2lvbk1lYXN1cmVtZW50IiwiZXhwaXJ5IjoxNjI2MjIwNzk5LCJpc1N1YmRvbWFpbiI6dHJ1ZSwiaXNUaGlyZFBhcnR5Ijp0cnVlfQ=="><meta http-equiv="origin-trial" content="AxoOxdZQmIoA1WeAPDixRAeWDdgs7ZtVFfH2y19ziTgD1iaHE5ZGz2UdSjubkWvob9C5PrjUfkWi4ZSLgWk3Xg8AAACLeyJvcmlnaW4iOiJodHRwczovL2dvb2dsZXRhZ3NlcnZpY2VzLmNvbTo0NDMiLCJmZWF0dXJlIjoiQ29udmVyc2lvbk1lYXN1cmVtZW50IiwiZXhwaXJ5IjoxNjI2MjIwNzk5LCJpc1N1YmRvbWFpbiI6dHJ1ZSwiaXNUaGlyZFBhcnR5Ijp0cnVlfQ=="><meta http-equiv="origin-trial" content="A7+rMYR5onPnACrz+niKSeFdH3xw1IyHo2AZSHmxrofRk9w4HcQPMYcpBUKu6OQ6zsdxf4m/vqa6tG6Na4OLpAQAAAB4eyJvcmlnaW4iOiJodHRwczovL2ltYXNkay5nb29nbGVhcGlzLmNvbTo0NDMiLCJmZWF0dXJlIjoiQ29udmVyc2lvbk1lYXN1cmVtZW50IiwiZXhwaXJ5IjoxNjI2MjIwNzk5LCJpc1RoaXJkUGFydHkiOnRydWV9"><meta http-equiv="origin-trial" content="AwfG8hAcHnPa/kJ1Co0EvG/K0F9l1s2JZGiDLt2mhC3QI5Fh4qmsmSwrWObZFbRC9ieDaSLU6lHRxhGUF/i9sgoAAACBeyJvcmlnaW4iOiJodHRwczovL2RvdWJsZWNsaWNrLm5ldDo0NDMiLCJmZWF0dXJlIjoiSW50ZXJlc3RDb2hvcnRBUEkiLCJleHBpcnkiOjE2MjYyMjA3OTksImlzU3ViZG9tYWluIjp0cnVlLCJpc1RoaXJkUGFydHkiOnRydWV9"><meta http-equiv="origin-trial" content="AwQ7dCmHkvR6FuOFxAuNnktYSQrGbL4dF+eBkrwNLALc69Wr//PnO1yzns3pjUoCaYbKHtVcnng2hU+8OUm0PAYAAACHeyJvcmlnaW4iOiJodHRwczovL2dvb2dsZXN5bmRpY2F0aW9uLmNvbTo0NDMiLCJmZWF0dXJlIjoiSW50ZXJlc3RDb2hvcnRBUEkiLCJleHBpcnkiOjE2MjYyMjA3OTksImlzU3ViZG9tYWluIjp0cnVlLCJpc1RoaXJkUGFydHkiOnRydWV9"><meta http-equiv="origin-trial" content="AysVDPGQTLD/Scn78x4mLwB1tMfje5jwUpAAzGRpWsr1NzoN7MTFhT3ClmImi2svDZA7V6nWGIV8YTPsSRTe0wYAAACHeyJvcmlnaW4iOiJodHRwczovL2dvb2dsZXRhZ3NlcnZpY2VzLmNvbTo0NDMiLCJmZWF0dXJlIjoiSW50ZXJlc3RDb2hvcnRBUEkiLCJleHBpcnkiOjE2MjYyMjA3OTksImlzU3ViZG9tYWluIjp0cnVlLCJpc1RoaXJkUGFydHkiOnRydWV9"><script src="./篇二_什么是ClickHouse的表引擎_ - 大数据技术与数仓 - 博客园_files/pubads_impl_2021062204.js.下载" async=""></script><link rel="preload" href="./篇二_什么是ClickHouse的表引擎_ - 大数据技术与数仓 - 博客园_files/f.txt" as="script"><script type="text/javascript" src="./篇二_什么是ClickHouse的表引擎_ - 大数据技术与数仓 - 博客园_files/f.txt"></script></head>
<body class="has-navbar">
    <a name="top"></a>
            <div id="bannerbar" class="bannerbar-mobile formobile">
            <a href="https://cloud.baidu.com/campaign/2021mid/index.html?track=cp:bokeyuan|pf:mb|pp:bokeyuan-huodong-21nianzhongdacu-neiyeyidongduantonglanbanner|pu:21nianzhongdacu-neiyeyidongduantonglanbanner|ci:21nzdc|kw:10224547" onclick="ga(&#39;send&#39;, &#39;event&#39;, &#39;Link&#39;, &#39;click&#39;, &#39;baidu-cpa-blog-bannerbar-mobile&#39;)">
                <img src="./篇二_什么是ClickHouse的表引擎_ - 大数据技术与数仓 - 博客园_files/35695-20210610184806729-1738888164.jpg" alt="">
            </a>
            </div>
    <div id="top_nav" class="navbar forpc">
        <nav id="nav_main" class="navbar-main">
            <ul id="nav_left" class="navbar-list navbar-left">
                <li class="navbar-branding"><a href="https://www.cnblogs.com/" title="开发者的网上家园"><img src="./篇二_什么是ClickHouse的表引擎_ - 大数据技术与数仓 - 博客园_files/logo.svg" alt="博客园Logo"></a></li>
                <li><a href="https://www.cnblogs.com/" onclick="ga(&#39;send&#39;, &#39;event&#39;, &#39;Link&#39;, &#39;click&#39;, &#39;skin-navbar-sitehome&#39;)">首页</a></li>
                <li><a href="https://news.cnblogs.com/" onclick="ga(&#39;send&#39;, &#39;event&#39;, &#39;Link&#39;, &#39;click&#39;, &#39;skin-navbar-news&#39;)">新闻</a></li>
                <li><a href="https://q.cnblogs.com/" onclick="ga(&#39;send&#39;, &#39;event&#39;, &#39;Link&#39;, &#39;click&#39;, &#39;skin-navbar-q&#39;)">博问</a></li>
                <li><a id="nav_brandzone" href="https://brands.cnblogs.com/" onclick="ga(&#39;send&#39;, &#39;event&#39;, &#39;Link&#39;, &#39;click&#39;, &#39;skin-navbar-brands&#39;)">专区</a></li>
                <li><a href="https://ing.cnblogs.com/" onclick="ga(&#39;send&#39;, &#39;event&#39;, &#39;Link&#39;, &#39;click&#39;, &#39;skin-navbar-ing&#39;)">闪存</a></li>
                <li><a href="https://edu.cnblogs.com/" onclick="ga(&#39;send&#39;, &#39;event&#39;, &#39;Link&#39;, &#39;click&#39;, &#39;skin-navbar-edu&#39;)">班级</a></li>
            </ul>
            <ul id="nav_right" class="navbar-list navbar-right">
                <li>
                    <form id="zzk_search" class="navbar-search" action="https://zzk.cnblogs.com/s" method="get">
                        <input name="w" id="zzk_search_input" placeholder="代码改变世界" type="text" tabindex="3">
                        <button type="submit" id="zzk_search_button">
                            <img src="./篇二_什么是ClickHouse的表引擎_ - 大数据技术与数仓 - 博客园_files/search.svg" alt="搜索">
                        </button>
                    </form>
                </li>
                <li id="navbar_login_status" class="navbar-list">
                    <a class="navbar-user-info navbar-blog" href="https://i.cnblogs.com/EditPosts.aspx?opt=1" alt="写随笔" title="写随笔" style="display: none;">
                        <img id="new_post_icon" class="navbar-icon" src="./篇二_什么是ClickHouse的表引擎_ - 大数据技术与数仓 - 博客园_files/newpost.svg" alt="写随笔">
                    </a>
                    <a id="navblog-myblog-icon" class="navbar-user-info navbar-blog" href="https://passport.cnblogs.com/GetBlogApplyStatus.aspx" alt="我的博客" title="我的博客" style="display: none;">
                        <img id="myblog_icon" class="navbar-icon" src="./篇二_什么是ClickHouse的表引擎_ - 大数据技术与数仓 - 博客园_files/myblog.svg" alt="我的博客">
                    </a>
                    <a class="navbar-user-info navbar-message navbar-icon-wrapper" href="https://msg.cnblogs.com/" alt="短消息" title="短消息" style="display: none;">
                        <img id="msg_icon" class="navbar-icon" src="./篇二_什么是ClickHouse的表引擎_ - 大数据技术与数仓 - 博客园_files/message.svg" alt="短消息">
                        <span id="msg_count" style="display: none"></span>
                    </a>
                    <div id="user_info" class="navbar-user-info dropdown" style="display: none;">
                        <a class="dropdown-button" href="https://home.cnblogs.com/">
                            <img id="user_icon" class="navbar-avatar" src="./篇二_什么是ClickHouse的表引擎_ - 大数据技术与数仓 - 博客园_files/avatar-default.svg" alt="用户头像">
                        </a>
                        <div class="dropdown-menu">
                            <a id="navblog-myblog-text" href="https://passport.cnblogs.com/GetBlogApplyStatus.aspx">我的博客</a>
                            <a href="https://home.cnblogs.com/">我的园子</a>
                            <a href="https://account.cnblogs.com/settings/account">账号设置</a>
                            <a href="javascript:void(0)" id="navbar_lite_mode_toggle" title="简洁模式会使用简洁款皮肤显示所有博客">
    简洁模式 <img id="navbar_lite_mode_on" src="./篇二_什么是ClickHouse的表引擎_ - 大数据技术与数仓 - 博客园_files/lite-mode-check.svg" class="hide"><span id="navbar_lite_mode_spinner" class="hide">...</span>
</a>
                            <a href="javascript:void(0)" onclick="account.logout();">退出登录</a>
                        </div>
                    </div>
                    <a class="navbar-anonymous" href="https://account.cnblogs.com/signup/" style="display: inline;">注册</a>
                    <a class="navbar-anonymous" href="javascript:void(0);" onclick="account.login()" style="display: inline;">登录</a>
                </li>
            </ul>
        </nav>
    </div>

    
    <!--done-->
<div id="home">
<div id="header">
	<div id="blogTitle">
        <a id="lnkBlogLogo" href="https://www.cnblogs.com/jmx-bigdata/"><img id="blogLogo" src="./篇二_什么是ClickHouse的表引擎_ - 大数据技术与数仓 - 博客园_files/logo.gif" alt="返回主页"></a>		
		
<!--done-->
<h1><a id="Header1_HeaderTitle" class="headermaintitle HeaderMainTitle" href="https://www.cnblogs.com/jmx-bigdata/">大数据技术与数仓</a>
</h1>
<h2>公众号『大数据技术与数仓』</h2>




		
	</div><!--end: blogTitle 博客的标题和副标题 -->
	<div id="navigator">
		
<ul id="navList">
<li><a id="blog_nav_sitehome" class="menu" href="https://www.cnblogs.com/">
博客园</a>
</li>
<li>
<a id="blog_nav_myhome" class="menu" href="https://www.cnblogs.com/jmx-bigdata/">
首页</a>
</li>
<li>

<a id="blog_nav_newpost" class="menu" href="https://i.cnblogs.com/EditPosts.aspx?opt=1">
新随笔</a>
</li>
<li>
<a id="blog_nav_contact" class="menu" href="https://msg.cnblogs.com/send/%E5%A4%A7%E6%95%B0%E6%8D%AE%E6%8A%80%E6%9C%AF%E4%B8%8E%E6%95%B0%E4%BB%93">
联系</a></li>
<li>
<a id="blog_nav_rss" class="menu" href="javascript:void(0)" data-rss="https://www.cnblogs.com/jmx-bigdata/rss/">
订阅</a>
<!--<partial name="./Shared/_XmlLink.cshtml" model="Model" /></li>--></li>
<li>
<a id="blog_nav_admin" class="menu" href="https://i.cnblogs.com/">
管理</a>
</li>
</ul>


		<div class="blogStats">
			<span id="stats_post_count">随笔 - 169&nbsp; </span>
<span id="stats_article_count">文章 - 1&nbsp; </span>
<span id="stats-comment_count">评论 - 5&nbsp; </span>
<span id="stats-total-view-count">阅读 - 
<span title="总阅读数: 49699">
49699</span></span>

		</div><!--end: blogStats -->
	</div><!--end: navigator 博客导航栏 -->
</div><!--end: header 头部 -->
<div id="main">
	<div id="mainContent">
	<div class="forFlow">
		<div id="post_detail">
    <!--done-->
    <div id="topics">
        <div class="post">
            <h1 class="postTitle">
                
<a id="cb_post_title_url" class="postTitle2 vertical-middle" href="https://www.cnblogs.com/jmx-bigdata/p/13708834.html">
    <span>篇二|什么是ClickHouse的表引擎?</span>
    



</a>

            </h1>
            <div class="clear"></div>
            <div class="postBody">
                <div id="cnblogs_post_body" class="blogpost-body blogpost-body-html">

                    <svg xmlns="http://www.w3.org/2000/svg" style="display: none">
                        <path stroke-linecap="round" d="M5,0 0,2.5 5,5z" id="raphael-marker-block" style="-webkit-tap-highlight-color: rgba(0, 0, 0, 0)"></path>
                    </svg>
                    <p>在上一篇分享中，我们介绍了ClickHouse的安装部署和简单使用。本文将介绍ClickHouse中一个非常重要的概念—<strong>表引擎(table engine)</strong>。如果对MySQL熟悉的话，或许你应该听说过InnoDB和MyISAM存储引擎。不同的存储引擎提供不同的存储机制、索引方式、锁定水平等功能，也可以称之为<strong>表类型</strong>。ClickHouse提供了丰富的表引擎，这些不同的表引擎也代表着不同的<strong>表类型</strong>。比如数据表拥有何种特性、数据以何种形式被存储以及如何被加载。本文会对ClickHouse中常见的表引擎进行介绍，主要包括以下内容：</p> 
<ul><li>表引擎的作用是什么</li><li>MergeTree系列引擎</li><li>Log家族系列引擎</li><li>外部集成表引擎</li><li>其他特殊的表引擎</li></ul> 
<blockquote> 
 <p>温馨提示：本文内容较长，建议收藏<br> <img src="./篇二_什么是ClickHouse的表引擎_ - 大数据技术与数仓 - 博客园_files/20200921212359303.png" alt="" class="medium-zoom-image"></p> 
</blockquote> 
<h2><a id="_11"></a>表引擎的作用是什么</h2> 
<ul><li>决定表存储在哪里以及以何种方式存储</li><li>支持哪些查询以及如何支持</li><li>并发数据访问</li><li>索引的使用</li><li>是否可以执行多线程请求</li><li>数据复制参数</li></ul> 
<h2><a id="_20"></a>表引擎分类</h2> 
<div class="table-wrapper"><table><thead><tr><th>引擎分类</th><th>引擎名称</th></tr></thead><tbody><tr><td>MergeTree系列</td><td>MergeTree 、ReplacingMergeTree 、SummingMergeTree 、 AggregatingMergeTree CollapsingMergeTree 、 VersionedCollapsingMergeTree 、GraphiteMergeTree</td></tr><tr><td>Log系列</td><td>TinyLog 、StripeLog 、Log</td></tr><tr><td>Integration Engines</td><td>Kafka 、MySQL、ODBC 、JDBC、HDFS</td></tr><tr><td>Special Engines</td><td>Distributed 、MaterializedView、 Dictionary 、Merge 、File、Null 、Set 、Join 、 URL View、Memory 、 Buffer</td></tr></tbody></table></div>
<h2><a id="Log_29"></a>Log系列表引擎</h2> 
<h3><a id="_31"></a>应用场景</h3> 
<p>Log系列表引擎功能相对简单，主要用于快速写入小表(1百万行左右的表)，然后全部读出的场景。<strong>即一次写入多次查询</strong>。</p> 
<h3><a id="Log_35"></a>Log系列表引擎的特点</h3> 
<h4><a id="_37"></a>共性特点</h4> 
<ul><li>数据存储在磁盘上</li><li>当写数据时，将数据追加到文件的末尾</li><li>不支持<strong>并发读写</strong>，当向表中写入数据时，针对这张表的查询会被阻塞，直至写入动作结束</li><li>不支持索引</li><li>不支持原子写：如果某些操作(异常的服务器关闭)中断了写操作，则可能会获得带有损坏数据的表</li><li>不支持<strong>ALTER操作</strong>(这些操作会修改表设置或数据，比如delete、update等等)</li></ul> 
<h4><a id="_46"></a>区别</h4> 
<ul><li> <p><strong>TinyLog</strong></p> <p>TinyLog是Log系列引擎中功能简单、性能较低的引擎。它的存储结构由数据文件和元数据两部分组成。其中，<strong>数据文件是按列独立存储的，也就是说每一个列字段都对应一个文件</strong>。除此之外，TinyLog不支持并发数据读取。</p> </li><li> <p><strong>StripLog</strong>支持并发读取数据文件，当读取数据时，ClickHouse会使用多线程进行读取，每个线程处理一个单独的数据块。另外，<strong>StripLog将所有列数据存储在同一个文件中</strong>，减少了文件的使用数量。</p> </li><li> <p><strong>Log</strong>支持并发读取数据文件，当读取数据时，ClickHouse会使用多线程进行读取，每个线程处理一个单独的数据块。<strong>Log引擎会将每个列数据单独存储在一个独立文件中</strong>。</p> </li></ul> 
<h3><a id="TinyLog_56"></a>TinyLog表引擎使用</h3> 
<p>该引擎适用于<strong>一次写入，多次读取的场景</strong>。对于处理小批数据的中间表可以使用该引擎。值得注意的是，使用大量的小表存储数据，性能会很低。</p> 
<pre class=" language-sql"><code class="prism  language-sql"><span class="token keyword">CREATE</span> <span class="token keyword">TABLE</span> emp_tinylog <span class="token punctuation">(</span>
  emp_id UInt16 <span class="token keyword">COMMENT</span> <span class="token string">'员工id'</span><span class="token punctuation">,</span>
  name String <span class="token keyword">COMMENT</span> <span class="token string">'员工姓名'</span><span class="token punctuation">,</span>
  work_place String <span class="token keyword">COMMENT</span> <span class="token string">'工作地点'</span><span class="token punctuation">,</span>
  age UInt8 <span class="token keyword">COMMENT</span> <span class="token string">'员工年龄'</span><span class="token punctuation">,</span>
  depart String <span class="token keyword">COMMENT</span> <span class="token string">'部门'</span><span class="token punctuation">,</span>
  salary Decimal32<span class="token punctuation">(</span><span class="token number">2</span><span class="token punctuation">)</span> <span class="token keyword">COMMENT</span> <span class="token string">'工资'</span>
  <span class="token punctuation">)</span><span class="token keyword">ENGINE</span><span class="token operator">=</span>TinyLog<span class="token punctuation">(</span><span class="token punctuation">)</span><span class="token punctuation">;</span>

<span class="token keyword">INSERT</span> <span class="token keyword">INTO</span> emp_tinylog 
<span class="token keyword">VALUES</span> <span class="token punctuation">(</span><span class="token number">1</span><span class="token punctuation">,</span><span class="token string">'tom'</span><span class="token punctuation">,</span><span class="token string">'上海'</span><span class="token punctuation">,</span><span class="token number">25</span><span class="token punctuation">,</span><span class="token string">'技术部'</span><span class="token punctuation">,</span><span class="token number">20000</span><span class="token punctuation">)</span><span class="token punctuation">,</span><span class="token punctuation">(</span><span class="token number">2</span><span class="token punctuation">,</span><span class="token string">'jack'</span><span class="token punctuation">,</span><span class="token string">'上海'</span><span class="token punctuation">,</span><span class="token number">26</span><span class="token punctuation">,</span><span class="token string">'人事部'</span><span class="token punctuation">,</span><span class="token number">10000</span><span class="token punctuation">)</span><span class="token punctuation">;</span>
<span class="token keyword">INSERT</span> <span class="token keyword">INTO</span> emp_tinylog
<span class="token keyword">VALUES</span> <span class="token punctuation">(</span><span class="token number">3</span><span class="token punctuation">,</span><span class="token string">'bob'</span><span class="token punctuation">,</span><span class="token string">'北京'</span><span class="token punctuation">,</span><span class="token number">33</span><span class="token punctuation">,</span><span class="token string">'财务部'</span><span class="token punctuation">,</span><span class="token number">50000</span><span class="token punctuation">)</span><span class="token punctuation">,</span><span class="token punctuation">(</span><span class="token number">4</span><span class="token punctuation">,</span><span class="token string">'tony'</span><span class="token punctuation">,</span><span class="token string">'杭州'</span><span class="token punctuation">,</span><span class="token number">28</span><span class="token punctuation">,</span><span class="token string">'销售事部'</span><span class="token punctuation">,</span><span class="token number">50000</span><span class="token punctuation">)</span><span class="token punctuation">;</span>
</code></pre> 
<p>进入默认数据存储目录，查看底层数据存储形式,可以看出：<strong>TinyLog</strong>引擎表每一列都对应的文件</p> 
<pre class=" language-bash"><code class="prism  language-bash"><span class="token punctuation">[</span>root@cdh04 emp_tinylog<span class="token punctuation">]</span><span class="token comment"># pwd</span>
/var/lib/clickhouse/data/default/emp_tinylog
<span class="token punctuation">[</span>root@cdh04 emp_tinylog<span class="token punctuation">]</span><span class="token comment"># ll</span>
总用量 <span class="token number">28</span>
-rw-r----- <span class="token number">1</span> clickhouse clickhouse  <span class="token number">56</span> <span class="token number">9</span>月  <span class="token number">17</span> <span class="token number">14</span>:33 age.bin
-rw-r----- <span class="token number">1</span> clickhouse clickhouse  <span class="token number">97</span> <span class="token number">9</span>月  <span class="token number">17</span> <span class="token number">14</span>:33 depart.bin
-rw-r----- <span class="token number">1</span> clickhouse clickhouse  <span class="token number">60</span> <span class="token number">9</span>月  <span class="token number">17</span> <span class="token number">14</span>:33 emp_id.bin
-rw-r----- <span class="token number">1</span> clickhouse clickhouse  <span class="token number">70</span> <span class="token number">9</span>月  <span class="token number">17</span> <span class="token number">14</span>:33 name.bin
-rw-r----- <span class="token number">1</span> clickhouse clickhouse  <span class="token number">68</span> <span class="token number">9</span>月  <span class="token number">17</span> <span class="token number">14</span>:33 salary.bin
-rw-r----- <span class="token number">1</span> clickhouse clickhouse <span class="token number">185</span> <span class="token number">9</span>月  <span class="token number">17</span> <span class="token number">14</span>:33 sizes.json
-rw-r----- <span class="token number">1</span> clickhouse clickhouse  <span class="token number">80</span> <span class="token number">9</span>月  <span class="token number">17</span> <span class="token number">14</span>:33 work_place.bin
<span class="token comment">## 查看sizes.json数据</span>
<span class="token comment">## 在sizes.json文件内使用JSON格式记录了每个．bin文件内对应的数据大小的信息</span>
<span class="token punctuation">{</span>
    <span class="token string">"yandex"</span>:<span class="token punctuation">{</span>
        <span class="token string">"age%2Ebin"</span>:<span class="token punctuation">{</span>
            <span class="token string">"size"</span><span class="token builtin class-name">:</span><span class="token string">"56"</span>
        <span class="token punctuation">}</span>,
        <span class="token string">"depart%2Ebin"</span>:<span class="token punctuation">{</span>
            <span class="token string">"size"</span><span class="token builtin class-name">:</span><span class="token string">"97"</span>
        <span class="token punctuation">}</span>,
        <span class="token string">"emp_id%2Ebin"</span>:<span class="token punctuation">{</span>
            <span class="token string">"size"</span><span class="token builtin class-name">:</span><span class="token string">"60"</span>
        <span class="token punctuation">}</span>,
        <span class="token string">"name%2Ebin"</span>:<span class="token punctuation">{</span>
            <span class="token string">"size"</span><span class="token builtin class-name">:</span><span class="token string">"70"</span>
        <span class="token punctuation">}</span>,
        <span class="token string">"salary%2Ebin"</span>:<span class="token punctuation">{</span>
            <span class="token string">"size"</span><span class="token builtin class-name">:</span><span class="token string">"68"</span>
        <span class="token punctuation">}</span>,
        <span class="token string">"work_place%2Ebin"</span>:<span class="token punctuation">{</span>
            <span class="token string">"size"</span><span class="token builtin class-name">:</span><span class="token string">"80"</span>
        <span class="token punctuation">}</span>
    <span class="token punctuation">}</span>
<span class="token punctuation">}</span>
</code></pre> 
<p>当我们执行<strong>ALTER操作</strong>时会报错，说明该表引擎不支持ALTER操作</p> 
<pre class=" language-sql"><code class="prism  language-sql"><span class="token comment">-- 以下操作会报错：</span>
<span class="token comment">-- DB::Exception: Mutations are not supported by storage TinyLog.</span>
<span class="token keyword">ALTER</span> <span class="token keyword">TABLE</span> emp_tinylog <span class="token keyword">DELETE</span> <span class="token keyword">WHERE</span> emp_id <span class="token operator">=</span> <span class="token number">5</span><span class="token punctuation">;</span>
<span class="token keyword">ALTER</span> <span class="token keyword">TABLE</span> emp_tinylog <span class="token keyword">UPDATE</span> age <span class="token operator">=</span> <span class="token number">30</span> <span class="token keyword">WHERE</span> emp_id <span class="token operator">=</span> <span class="token number">4</span><span class="token punctuation">;</span>
</code></pre> 
<h3><a id="StripLog_125"></a>StripLog表引擎使用</h3> 
<p>相比TinyLog而言，StripeLog拥有更高的查询性能（拥有.mrk标记文件，支持并行查询），同时其使用了更少的文件描述符（所有数据使用同一个文件保存）。</p> 
<pre class=" language-sql"><code class="prism  language-sql"><span class="token keyword">CREATE</span> <span class="token keyword">TABLE</span> emp_stripelog <span class="token punctuation">(</span>
  emp_id UInt16 <span class="token keyword">COMMENT</span> <span class="token string">'员工id'</span><span class="token punctuation">,</span>
  name String <span class="token keyword">COMMENT</span> <span class="token string">'员工姓名'</span><span class="token punctuation">,</span>
  work_place String <span class="token keyword">COMMENT</span> <span class="token string">'工作地点'</span><span class="token punctuation">,</span>
  age UInt8 <span class="token keyword">COMMENT</span> <span class="token string">'员工年龄'</span><span class="token punctuation">,</span>
  depart String <span class="token keyword">COMMENT</span> <span class="token string">'部门'</span><span class="token punctuation">,</span>
  salary Decimal32<span class="token punctuation">(</span><span class="token number">2</span><span class="token punctuation">)</span> <span class="token keyword">COMMENT</span> <span class="token string">'工资'</span>
  <span class="token punctuation">)</span><span class="token keyword">ENGINE</span><span class="token operator">=</span>StripeLog<span class="token punctuation">;</span>
<span class="token comment">-- 插入数据  </span>
<span class="token keyword">INSERT</span> <span class="token keyword">INTO</span> emp_stripelog
<span class="token keyword">VALUES</span> <span class="token punctuation">(</span><span class="token number">1</span><span class="token punctuation">,</span><span class="token string">'tom'</span><span class="token punctuation">,</span><span class="token string">'上海'</span><span class="token punctuation">,</span><span class="token number">25</span><span class="token punctuation">,</span><span class="token string">'技术部'</span><span class="token punctuation">,</span><span class="token number">20000</span><span class="token punctuation">)</span><span class="token punctuation">,</span><span class="token punctuation">(</span><span class="token number">2</span><span class="token punctuation">,</span><span class="token string">'jack'</span><span class="token punctuation">,</span><span class="token string">'上海'</span><span class="token punctuation">,</span><span class="token number">26</span><span class="token punctuation">,</span><span class="token string">'人事部'</span><span class="token punctuation">,</span><span class="token number">10000</span><span class="token punctuation">)</span><span class="token punctuation">;</span>
<span class="token keyword">INSERT</span> <span class="token keyword">INTO</span> emp_stripelog 
<span class="token keyword">VALUES</span> <span class="token punctuation">(</span><span class="token number">3</span><span class="token punctuation">,</span><span class="token string">'bob'</span><span class="token punctuation">,</span><span class="token string">'北京'</span><span class="token punctuation">,</span><span class="token number">33</span><span class="token punctuation">,</span><span class="token string">'财务部'</span><span class="token punctuation">,</span><span class="token number">50000</span><span class="token punctuation">)</span><span class="token punctuation">,</span><span class="token punctuation">(</span><span class="token number">4</span><span class="token punctuation">,</span><span class="token string">'tony'</span><span class="token punctuation">,</span><span class="token string">'杭州'</span><span class="token punctuation">,</span><span class="token number">28</span><span class="token punctuation">,</span><span class="token string">'销售事部'</span><span class="token punctuation">,</span><span class="token number">50000</span><span class="token punctuation">)</span><span class="token punctuation">;</span>
<span class="token comment">-- 查询数据</span>
<span class="token comment">-- 由于是分两次插入数据，所以查询时会有两个数据块</span>
cdh04 :<span class="token punctuation">)</span> <span class="token keyword">select</span> <span class="token operator">*</span> <span class="token keyword">from</span> emp_stripelog<span class="token punctuation">;</span>

<span class="token keyword">SELECT</span> <span class="token operator">*</span>
<span class="token keyword">FROM</span> emp_stripelog

┌─emp_id─┬─name─┬─work_place─┬─age─┬─depart─┬───salary─┐
│      <span class="token number">1</span> │ tom  │ 上海       │  <span class="token number">25</span> │ 技术部 │ <span class="token number">20000.00</span> │
│      <span class="token number">2</span> │ jack │ 上海       │  <span class="token number">26</span> │ 人事部 │ <span class="token number">10000.00</span> │
└────────┴──────┴────────────┴─────┴────────┴──────────┘
┌─emp_id─┬─name─┬─work_place─┬─age─┬─depart───┬───salary─┐
│      <span class="token number">3</span> │ bob  │ 北京       │  <span class="token number">33</span> │ 财务部   │ <span class="token number">50000.00</span> │
│      <span class="token number">4</span> │ tony │ 杭州       │  <span class="token number">28</span> │ 销售事部 │ <span class="token number">50000.00</span> │
└────────┴──────┴────────────┴─────┴──────────┴──────────┘
</code></pre> 
<p>进入默认数据存储目录，查看底层数据存储形式</p> 
<pre class=" language-bash"><code class="prism  language-bash"><span class="token punctuation">[</span>root@cdh04 emp_stripelog<span class="token punctuation">]</span><span class="token comment"># pwd</span>
/var/lib/clickhouse/data/default/emp_stripelog
<span class="token punctuation">[</span>root@cdh04 emp_stripelog<span class="token punctuation">]</span><span class="token comment"># ll</span>
总用量 <span class="token number">12</span>
-rw-r----- <span class="token number">1</span> clickhouse clickhouse <span class="token number">673</span> <span class="token number">9</span>月  <span class="token number">17</span> <span class="token number">15</span>:11 data.bin
-rw-r----- <span class="token number">1</span> clickhouse clickhouse <span class="token number">281</span> <span class="token number">9</span>月  <span class="token number">17</span> <span class="token number">15</span>:11 index.mrk
-rw-r----- <span class="token number">1</span> clickhouse clickhouse  <span class="token number">69</span> <span class="token number">9</span>月  <span class="token number">17</span> <span class="token number">15</span>:11 sizes.json
</code></pre> 
<p>可以看出StripeLog表引擎对应的存储结构包括三个文件：</p> 
<ul><li>data.bin：数据文件，所有的列字段使用同一个文件保存，它们的数据都会被写入data.bin。</li><li>index.mrk：数据标记，保存了数据在data.bin文件中的位置信息(每个插入数据块对应列的offset)，利用数据标记能够使用多个线程，以并行的方式读取data.bin内的压缩数据块，从而提升数据查询的性能。</li><li>sizes.json：元数据文件，记录了data.bin和index.mrk大小的信息</li></ul> 
<blockquote> 
 <p>提示：</p> 
 <p><code>StripeLog</code>引擎将所有数据都存储在了一个文件中，对于每次的<strong>INSERT</strong>操作，ClickHouse会将<strong>数据块</strong>追加到表文件的末尾</p> 
 <p>StripeLog引擎同样不支持<code>ALTER UPDATE</code> 和<code>ALTER DELETE</code> 操作</p> 
</blockquote> 
<h3><a id="Log_184"></a>Log表引擎使用</h3> 
<p>Log引擎表适用于临时数据，一次性写入、测试场景。Log引擎结合了TinyLog表引擎和StripeLog表引擎的长处，是Log系列引擎中性能最高的表引擎。</p> 
<pre class=" language-sql"><code class="prism  language-sql"><span class="token keyword">CREATE</span> <span class="token keyword">TABLE</span> emp_log <span class="token punctuation">(</span>
  emp_id UInt16 <span class="token keyword">COMMENT</span> <span class="token string">'员工id'</span><span class="token punctuation">,</span>
  name String <span class="token keyword">COMMENT</span> <span class="token string">'员工姓名'</span><span class="token punctuation">,</span>
  work_place String <span class="token keyword">COMMENT</span> <span class="token string">'工作地点'</span><span class="token punctuation">,</span>
  age UInt8 <span class="token keyword">COMMENT</span> <span class="token string">'员工年龄'</span><span class="token punctuation">,</span>
  depart String <span class="token keyword">COMMENT</span> <span class="token string">'部门'</span><span class="token punctuation">,</span>
  salary Decimal32<span class="token punctuation">(</span><span class="token number">2</span><span class="token punctuation">)</span> <span class="token keyword">COMMENT</span> <span class="token string">'工资'</span>
  <span class="token punctuation">)</span><span class="token keyword">ENGINE</span><span class="token operator">=</span>Log<span class="token punctuation">;</span>
  
<span class="token keyword">INSERT</span> <span class="token keyword">INTO</span> emp_log <span class="token keyword">VALUES</span> <span class="token punctuation">(</span><span class="token number">1</span><span class="token punctuation">,</span><span class="token string">'tom'</span><span class="token punctuation">,</span><span class="token string">'上海'</span><span class="token punctuation">,</span><span class="token number">25</span><span class="token punctuation">,</span><span class="token string">'技术部'</span><span class="token punctuation">,</span><span class="token number">20000</span><span class="token punctuation">)</span><span class="token punctuation">,</span><span class="token punctuation">(</span><span class="token number">2</span><span class="token punctuation">,</span><span class="token string">'jack'</span><span class="token punctuation">,</span><span class="token string">'上海'</span><span class="token punctuation">,</span><span class="token number">26</span><span class="token punctuation">,</span><span class="token string">'人事部'</span><span class="token punctuation">,</span><span class="token number">10000</span><span class="token punctuation">)</span><span class="token punctuation">;</span>
<span class="token keyword">INSERT</span> <span class="token keyword">INTO</span> emp_log <span class="token keyword">VALUES</span> <span class="token punctuation">(</span><span class="token number">3</span><span class="token punctuation">,</span><span class="token string">'bob'</span><span class="token punctuation">,</span><span class="token string">'北京'</span><span class="token punctuation">,</span><span class="token number">33</span><span class="token punctuation">,</span><span class="token string">'财务部'</span><span class="token punctuation">,</span><span class="token number">50000</span><span class="token punctuation">)</span><span class="token punctuation">,</span><span class="token punctuation">(</span><span class="token number">4</span><span class="token punctuation">,</span><span class="token string">'tony'</span><span class="token punctuation">,</span><span class="token string">'杭州'</span><span class="token punctuation">,</span><span class="token number">28</span><span class="token punctuation">,</span><span class="token string">'销售事部'</span><span class="token punctuation">,</span><span class="token number">50000</span><span class="token punctuation">)</span><span class="token punctuation">;</span>
<span class="token comment">-- 查询数据，</span>
<span class="token comment">-- 由于是分两次插入数据，所以查询时会有两个数据块</span>
cdh04 :<span class="token punctuation">)</span> <span class="token keyword">select</span> <span class="token operator">*</span> <span class="token keyword">from</span> emp_log<span class="token punctuation">;</span>

<span class="token keyword">SELECT</span> <span class="token operator">*</span>
<span class="token keyword">FROM</span> emp_log

┌─emp_id─┬─name─┬─work_place─┬─age─┬─depart─┬───salary─┐
│      <span class="token number">1</span> │ tom  │ 上海       │  <span class="token number">25</span> │ 技术部 │ <span class="token number">20000.00</span> │
│      <span class="token number">2</span> │ jack │ 上海       │  <span class="token number">26</span> │ 人事部 │ <span class="token number">10000.00</span> │
└────────┴──────┴────────────┴─────┴────────┴──────────┘
┌─emp_id─┬─name─┬─work_place─┬─age─┬─depart───┬───salary─┐
│      <span class="token number">3</span> │ bob  │ 北京       │  <span class="token number">33</span> │ 财务部   │ <span class="token number">50000.00</span> │
│      <span class="token number">4</span> │ tony │ 杭州       │  <span class="token number">28</span> │ 销售事部 │ <span class="token number">50000.00</span> │
└────────┴──────┴────────────┴─────┴──────────┴──────────┘

</code></pre> 
<p>进入默认数据存储目录，查看底层数据存储形式</p> 
<pre class=" language-bash"><code class="prism  language-bash"><span class="token punctuation">[</span>root@cdh04 emp_log<span class="token punctuation">]</span><span class="token comment"># pwd</span>
/var/lib/clickhouse/data/default/emp_log
<span class="token punctuation">[</span>root@cdh04 emp_log<span class="token punctuation">]</span><span class="token comment"># ll</span>
总用量 <span class="token number">32</span>
-rw-r----- <span class="token number">1</span> clickhouse clickhouse  <span class="token number">56</span> <span class="token number">9</span>月  <span class="token number">17</span> <span class="token number">15</span>:55 age.bin
-rw-r----- <span class="token number">1</span> clickhouse clickhouse  <span class="token number">97</span> <span class="token number">9</span>月  <span class="token number">17</span> <span class="token number">15</span>:55 depart.bin
-rw-r----- <span class="token number">1</span> clickhouse clickhouse  <span class="token number">60</span> <span class="token number">9</span>月  <span class="token number">17</span> <span class="token number">15</span>:55 emp_id.bin
-rw-r----- <span class="token number">1</span> clickhouse clickhouse <span class="token number">192</span> <span class="token number">9</span>月  <span class="token number">17</span> <span class="token number">15</span>:55 __marks.mrk
-rw-r----- <span class="token number">1</span> clickhouse clickhouse  <span class="token number">70</span> <span class="token number">9</span>月  <span class="token number">17</span> <span class="token number">15</span>:55 name.bin
-rw-r----- <span class="token number">1</span> clickhouse clickhouse  <span class="token number">68</span> <span class="token number">9</span>月  <span class="token number">17</span> <span class="token number">15</span>:55 salary.bin
-rw-r----- <span class="token number">1</span> clickhouse clickhouse <span class="token number">216</span> <span class="token number">9</span>月  <span class="token number">17</span> <span class="token number">15</span>:55 sizes.json
-rw-r----- <span class="token number">1</span> clickhouse clickhouse  <span class="token number">80</span> <span class="token number">9</span>月  <span class="token number">17</span> <span class="token number">15</span>:55 work_place.bin
</code></pre> 
<p>Log引擎的存储结构包含三部分：</p> 
<ul><li>列.bin：数据文件，数据文件按列单独存储</li><li>__marks.mrk：数据标记，统一保存了数据在各个.bin文件中的位置信息。利用数据标记能够使用多个线程，以并行的方式读取。.bin内的压缩数据块，从而提升数据查询的性能。</li><li>sizes.json：记录了.bin和__marks.mrk大小的信息</li></ul> 
<blockquote> 
 <p>提示：</p> 
 <p>Log表引擎会将每一列都存在一个文件中，对于每一次的INSERT操作，都会对应一个数据块</p> 
</blockquote> 
<h2><a id="MergeTree_245"></a>MergeTree系列引擎</h2> 
<p>在所有的表引擎中，最为核心的当属MergeTree系列表引擎，这些表引擎拥有最为强大的性能和最广泛的使用场合。对于非MergeTree系列的其他引擎而言，主要用于特殊用途，场景相对有限。而MergeTree系列表引擎是官方主推的存储引擎，支持几乎所有ClickHouse核心功能。</p> 
<h3><a id="MergeTree_249"></a>MergeTree表引擎</h3> 
<p>MergeTree在写入一批数据时，数据总会以数据片段的形式写入磁盘，且数据片段不可修改。为了避免片段过多，ClickHouse会通过后台线程，定期合并这些数据片段，属于相同分区的数据片段会被合成一个新的片段。这种数据片段往复合并的特点，也正是合并树名称的由来。</p> 
<p>MergeTree作为家族系列最基础的表引擎，主要有以下特点：</p> 
<ul><li>存储的数据按照主键排序：允许创建稀疏索引，从而加快数据查询速度</li><li>支持分区，可以通过PRIMARY KEY语句指定分区字段。</li><li>支持数据副本</li><li>支持数据采样</li></ul> 
<h4><a id="_260"></a>建表语法</h4> 
<pre class=" language-sql"><code class="prism  language-sql"><span class="token keyword">CREATE</span> <span class="token keyword">TABLE</span> <span class="token punctuation">[</span><span class="token keyword">IF</span> <span class="token operator">NOT</span> <span class="token keyword">EXISTS</span><span class="token punctuation">]</span> <span class="token punctuation">[</span>db<span class="token punctuation">.</span><span class="token punctuation">]</span>table_name <span class="token punctuation">[</span><span class="token keyword">ON</span> CLUSTER cluster<span class="token punctuation">]</span>
<span class="token punctuation">(</span>
    name1 <span class="token punctuation">[</span>type1<span class="token punctuation">]</span> <span class="token punctuation">[</span><span class="token keyword">DEFAULT</span><span class="token operator">|</span>MATERIALIZED<span class="token operator">|</span>ALIAS expr1<span class="token punctuation">]</span> <span class="token punctuation">[</span>TTL expr1<span class="token punctuation">]</span><span class="token punctuation">,</span>
    name2 <span class="token punctuation">[</span>type2<span class="token punctuation">]</span> <span class="token punctuation">[</span><span class="token keyword">DEFAULT</span><span class="token operator">|</span>MATERIALIZED<span class="token operator">|</span>ALIAS expr2<span class="token punctuation">]</span> <span class="token punctuation">[</span>TTL expr2<span class="token punctuation">]</span><span class="token punctuation">,</span>
    <span class="token punctuation">.</span><span class="token punctuation">.</span><span class="token punctuation">.</span>
    <span class="token keyword">INDEX</span> index_name1 expr1 <span class="token keyword">TYPE</span> type1<span class="token punctuation">(</span><span class="token punctuation">.</span><span class="token punctuation">.</span><span class="token punctuation">.</span><span class="token punctuation">)</span> GRANULARITY value1<span class="token punctuation">,</span>
    <span class="token keyword">INDEX</span> index_name2 expr2 <span class="token keyword">TYPE</span> type2<span class="token punctuation">(</span><span class="token punctuation">.</span><span class="token punctuation">.</span><span class="token punctuation">.</span><span class="token punctuation">)</span> GRANULARITY value2
<span class="token punctuation">)</span> <span class="token keyword">ENGINE</span> <span class="token operator">=</span> MergeTree<span class="token punctuation">(</span><span class="token punctuation">)</span>
<span class="token keyword">ORDER</span> <span class="token keyword">BY</span> expr
<span class="token punctuation">[</span><span class="token keyword">PARTITION</span> <span class="token keyword">BY</span> expr<span class="token punctuation">]</span>
<span class="token punctuation">[</span><span class="token keyword">PRIMARY</span> <span class="token keyword">KEY</span> expr<span class="token punctuation">]</span>
<span class="token punctuation">[</span>SAMPLE <span class="token keyword">BY</span> expr<span class="token punctuation">]</span>
<span class="token punctuation">[</span>TTL expr <span class="token punctuation">[</span><span class="token keyword">DELETE</span><span class="token operator">|</span><span class="token keyword">TO</span> <span class="token keyword">DISK</span> <span class="token string">'xxx'</span><span class="token operator">|</span><span class="token keyword">TO</span> VOLUME <span class="token string">'xxx'</span><span class="token punctuation">]</span><span class="token punctuation">,</span> <span class="token punctuation">.</span><span class="token punctuation">.</span><span class="token punctuation">.</span><span class="token punctuation">]</span>
<span class="token punctuation">[</span>SETTINGS name<span class="token operator">=</span><span class="token keyword">value</span><span class="token punctuation">,</span> <span class="token punctuation">.</span><span class="token punctuation">.</span><span class="token punctuation">.</span><span class="token punctuation">]</span>
</code></pre> 
<ul><li><strong>ENGINE</strong>：ENGINE = MergeTree()，MergeTree引擎没有参数</li><li><strong>ORDER BY</strong>：排序字段。比如ORDER BY (Col1, Col2)，值得注意的是，如果没有指定主键，默认情况下 sorting key(排序字段)即为主键。如果不需要排序，则可以使用**ORDER BY tuple()**语法，这样的话，创建的表也就不包含主键。这种情况下，ClickHouse会按照插入的顺序存储数据。<strong>必选</strong>。</li><li><strong>PARTITION BY</strong>：分区字段，<strong>可选</strong>。</li><li><strong>PRIMARY KEY</strong>：指定主键，如果排序字段与主键不一致，可以单独指定主键字段。否则默认主键是排序字段。<strong>可选</strong>。</li><li><strong>SAMPLE BY</strong>：采样字段，如果指定了该字段，那么主键中也必须包含该字段。比如<code>SAMPLE BY intHash32(UserID) ORDER BY (CounterID, EventDate, intHash32(UserID))</code>。<strong>可选</strong>。</li><li><strong>TTL</strong>：数据的存活时间。在MergeTree中，可以为某个列字段或整张表设置TTL。当时间到达时，如果是列字段级别的TTL，则会删除这一列的数据；如果是表级别的TTL，则会删除整张表的数据。<strong>可选</strong>。</li><li><strong>SETTINGS</strong>：额外的参数配置。<strong>可选</strong>。</li></ul> 
<h4><a id="_287"></a>建表示例</h4> 
<pre class=" language-sql"><code class="prism  language-sql"><span class="token keyword">CREATE</span> <span class="token keyword">TABLE</span> emp_mergetree <span class="token punctuation">(</span>
  emp_id UInt16 <span class="token keyword">COMMENT</span> <span class="token string">'员工id'</span><span class="token punctuation">,</span>
  name String <span class="token keyword">COMMENT</span> <span class="token string">'员工姓名'</span><span class="token punctuation">,</span>
  work_place String <span class="token keyword">COMMENT</span> <span class="token string">'工作地点'</span><span class="token punctuation">,</span>
  age UInt8 <span class="token keyword">COMMENT</span> <span class="token string">'员工年龄'</span><span class="token punctuation">,</span>
  depart String <span class="token keyword">COMMENT</span> <span class="token string">'部门'</span><span class="token punctuation">,</span>
  salary Decimal32<span class="token punctuation">(</span><span class="token number">2</span><span class="token punctuation">)</span> <span class="token keyword">COMMENT</span> <span class="token string">'工资'</span>
  <span class="token punctuation">)</span><span class="token keyword">ENGINE</span><span class="token operator">=</span>MergeTree<span class="token punctuation">(</span><span class="token punctuation">)</span>
  <span class="token keyword">ORDER</span> <span class="token keyword">BY</span> emp_id
  <span class="token keyword">PARTITION</span> <span class="token keyword">BY</span> work_place
  <span class="token punctuation">;</span>
 <span class="token comment">-- 插入数据 </span>
<span class="token keyword">INSERT</span> <span class="token keyword">INTO</span> emp_mergetree 
<span class="token keyword">VALUES</span> <span class="token punctuation">(</span><span class="token number">1</span><span class="token punctuation">,</span><span class="token string">'tom'</span><span class="token punctuation">,</span><span class="token string">'上海'</span><span class="token punctuation">,</span><span class="token number">25</span><span class="token punctuation">,</span><span class="token string">'技术部'</span><span class="token punctuation">,</span><span class="token number">20000</span><span class="token punctuation">)</span><span class="token punctuation">,</span><span class="token punctuation">(</span><span class="token number">2</span><span class="token punctuation">,</span><span class="token string">'jack'</span><span class="token punctuation">,</span><span class="token string">'上海'</span><span class="token punctuation">,</span><span class="token number">26</span><span class="token punctuation">,</span><span class="token string">'人事部'</span><span class="token punctuation">,</span><span class="token number">10000</span><span class="token punctuation">)</span><span class="token punctuation">;</span>
<span class="token keyword">INSERT</span> <span class="token keyword">INTO</span> emp_mergetree 
<span class="token keyword">VALUES</span> <span class="token punctuation">(</span><span class="token number">3</span><span class="token punctuation">,</span><span class="token string">'bob'</span><span class="token punctuation">,</span><span class="token string">'北京'</span><span class="token punctuation">,</span><span class="token number">33</span><span class="token punctuation">,</span><span class="token string">'财务部'</span><span class="token punctuation">,</span><span class="token number">50000</span><span class="token punctuation">)</span><span class="token punctuation">,</span><span class="token punctuation">(</span><span class="token number">4</span><span class="token punctuation">,</span><span class="token string">'tony'</span><span class="token punctuation">,</span><span class="token string">'杭州'</span><span class="token punctuation">,</span><span class="token number">28</span><span class="token punctuation">,</span><span class="token string">'销售事部'</span><span class="token punctuation">,</span><span class="token number">50000</span><span class="token punctuation">)</span><span class="token punctuation">;</span> 

<span class="token comment">-- 查询数据</span>
<span class="token comment">-- 按work_place进行分区</span>
cdh04 :<span class="token punctuation">)</span> <span class="token keyword">select</span> <span class="token operator">*</span> <span class="token keyword">from</span> emp_mergetree<span class="token punctuation">;</span>

<span class="token keyword">SELECT</span> <span class="token operator">*</span>
<span class="token keyword">FROM</span> emp_mergetree

┌─emp_id─┬─name─┬─work_place─┬─age─┬─depart─┬───salary─┐
│      <span class="token number">3</span> │ bob  │ 北京       │  <span class="token number">33</span> │ 财务部 │ <span class="token number">50000.00</span> │
└────────┴──────┴────────────┴─────┴────────┴──────────┘
┌─emp_id─┬─name─┬─work_place─┬─age─┬─depart─┬───salary─┐
│      <span class="token number">1</span> │ tom  │ 上海       │  <span class="token number">25</span> │ 技术部 │ <span class="token number">20000.00</span> │
│      <span class="token number">2</span> │ jack │ 上海       │  <span class="token number">26</span> │ 人事部 │ <span class="token number">10000.00</span> │
└────────┴──────┴────────────┴─────┴────────┴──────────┘
┌─emp_id─┬─name─┬─work_place─┬─age─┬─depart───┬───salary─┐
│      <span class="token number">4</span> │ tony │ 杭州       │  <span class="token number">28</span> │ 销售事部 │ <span class="token number">50000.00</span> │
└────────┴──────┴────────────┴─────┴──────────┴──────────┘
</code></pre> 
<p>查看一下数据存储格式，可以看出，存在三个分区文件夹，每一个分区文件夹内存储了对应分区的数据。</p> 
<pre class=" language-sql"><code class="prism  language-sql"><span class="token punctuation">[</span>root<span class="token variable">@cdh04</span> emp_mergetree<span class="token punctuation">]</span><span class="token comment"># pwd</span>
<span class="token operator">/</span>var<span class="token operator">/</span>lib<span class="token operator">/</span>clickhouse<span class="token operator">/</span><span class="token keyword">data</span><span class="token operator">/</span><span class="token keyword">default</span><span class="token operator">/</span>emp_mergetree
<span class="token punctuation">[</span>root<span class="token variable">@cdh04</span> emp_mergetree<span class="token punctuation">]</span><span class="token comment"># ll</span>
总用量 <span class="token number">16</span>
drwxr<span class="token operator">-</span>x<span class="token comment">--- 2 clickhouse clickhouse 4096 9月  17 17:45 1c89a3ba9fe5fd53379716a776c5ac34_3_3_0</span>
drwxr<span class="token operator">-</span>x<span class="token comment">--- 2 clickhouse clickhouse 4096 9月  17 17:44 40d45822dbd7fa81583d715338929da9_1_1_0</span>
drwxr<span class="token operator">-</span>x<span class="token comment">--- 2 clickhouse clickhouse 4096 9月  17 17:45 a6155dcc1997eda1a348cd98b17a93e9_2_2_0</span>
drwxr<span class="token operator">-</span>x<span class="token comment">--- 2 clickhouse clickhouse    6 9月  17 17:43 detached</span>
<span class="token operator">-</span>rw<span class="token operator">-</span>r<span class="token comment">----- 1 clickhouse clickhouse    1 9月  17 17:43 format_version.txt</span>
</code></pre> 
<p>进入一个分区目录查看</p> 
<p><img src="./篇二_什么是ClickHouse的表引擎_ - 大数据技术与数仓 - 博客园_files/20200921212512303.png" alt="" class="medium-zoom-image"></p> 
<ul><li> <p><strong>checksums.txt</strong>：校验文件，使用二进制格式存储。它保存了余下各类文件(primary. idx、count.txt等)的size大小及size的哈希值，用于快速校验文件的完整性和正确性。</p> </li><li> <p><strong>columns.txt</strong>：列信息文件，使用明文格式存储。用于保存此数据分区下的列字段信息，例如</p> <pre class=" language-bash"><code class="prism  language-bash"><span class="token punctuation">[</span>root@cdh04 1c89a3ba9fe5fd53379716a776c5ac34_3_3_0<span class="token punctuation">]</span><span class="token comment"># cat columns.txt</span>
columns <span class="token function">format</span> version: <span class="token number">1</span>
<span class="token number">6</span> columns:
<span class="token variable"><span class="token variable">`</span>emp_id<span class="token variable">`</span></span> UInt16
<span class="token variable"><span class="token variable">`</span>name<span class="token variable">`</span></span> String
<span class="token variable"><span class="token variable">`</span>work_place<span class="token variable">`</span></span> String
<span class="token variable"><span class="token variable">`</span>age<span class="token variable">`</span></span> UInt8
<span class="token variable"><span class="token variable">`</span>depart<span class="token variable">`</span></span> String
<span class="token variable"><span class="token variable">`</span>salary<span class="token variable">`</span></span> Decimal<span class="token punctuation">(</span><span class="token number">9</span>, <span class="token number">2</span><span class="token punctuation">)</span>
</code></pre> </li><li> <p><strong>count.txt</strong>：计数文件，使用明文格式存储。用于记录当前数据分区目录下数据的总行数</p> </li><li> <p><strong>primary.idx</strong>：一级索引文件，使用二进制格式存储。用于存放稀疏索引，一张MergeTree表只能声明一次一级索引，<strong>即通过ORDER BY或者PRIMARY KEY</strong>指定字段。借助稀疏索引，在数据查询的时能够排除主键条件范围之外的数据文件，从而有效减少数据扫描范围，加速查询速度。</p> </li><li> <p><strong>列.bin</strong>：数据文件，使用压缩格式存储，默认为LZ4压缩格式，用于存储某一列的数据。由于MergeTree采用列式存储，所以每一个列字段都拥有独立的<code>.bin</code>数据文件，并以列字段名称命名。</p> </li><li> <p><strong>列.mrk2</strong>：列字段标记文件，使用二进制格式存储。标记文件中保存了<code>.bin</code>文件中数据的偏移量信息</p> </li><li> <p><strong>partition.dat与minmax_[Column].idx</strong>：如果指定了分区键，则会额外生成partition.dat与minmax索引文件，它们均使用二进制格式存储。<strong>partition.dat</strong>用于保存当前分区下分区表达式最终生成的值，即分区字段值；而<strong>minmax</strong>索引用于记录当前分区下分区字段对应原始数据的最小和最大值。比如当使用EventTime字段对应的原始数据为2020-09-17、2020-09-30，分区表达式为PARTITION BY toYYYYMM(EventTime)，即按月分区。partition.dat中保存的值将会是2019-09，而minmax索引中保存的值将会是2020-09-17 2020-09-30。</p> </li></ul> 
<h4><a id="_371"></a>注意点</h4> 
<ul><li>多次插入数据，会生成多个分区文件</li></ul> 
<pre class=" language-sql"><code class="prism  language-sql"><span class="token comment">-- 新插入两条数据</span>
cdh04 :<span class="token punctuation">)</span> <span class="token keyword">INSERT</span> <span class="token keyword">INTO</span> emp_mergetree
<span class="token keyword">VALUES</span> <span class="token punctuation">(</span><span class="token number">5</span><span class="token punctuation">,</span><span class="token string">'robin'</span><span class="token punctuation">,</span><span class="token string">'北京'</span><span class="token punctuation">,</span><span class="token number">35</span><span class="token punctuation">,</span><span class="token string">'财务部'</span><span class="token punctuation">,</span><span class="token number">50000</span><span class="token punctuation">)</span><span class="token punctuation">,</span><span class="token punctuation">(</span><span class="token number">6</span><span class="token punctuation">,</span><span class="token string">'lilei'</span><span class="token punctuation">,</span><span class="token string">'北京'</span><span class="token punctuation">,</span><span class="token number">38</span><span class="token punctuation">,</span><span class="token string">'销售事部'</span><span class="token punctuation">,</span><span class="token number">50000</span><span class="token punctuation">)</span><span class="token punctuation">;</span>

<span class="token comment">-- 查询结果</span>
cdh04 :<span class="token punctuation">)</span> <span class="token keyword">select</span> <span class="token operator">*</span> <span class="token keyword">from</span> emp_mergetree<span class="token punctuation">;</span>
┌─emp_id─┬─name─┬─work_place─┬─age─┬─depart─┬───salary─┐
│      <span class="token number">3</span> │ bob  │ 北京       │  <span class="token number">33</span> │ 财务部 │ <span class="token number">50000.00</span> │
└────────┴──────┴────────────┴─────┴────────┴──────────┘
┌─emp_id─┬─name──┬─work_place─┬─age─┬─depart───┬───salary─┐
│      <span class="token number">5</span> │ robin │ 北京       │  <span class="token number">35</span> │ 财务部   │ <span class="token number">50000.00</span> │
│      <span class="token number">6</span> │ lilei │ 北京       │  <span class="token number">38</span> │ 销售事部 │ <span class="token number">50000.00</span> │
└────────┴───────┴────────────┴─────┴──────────┴──────────┘
┌─emp_id─┬─name─┬─work_place─┬─age─┬─depart─┬───salary─┐
│      <span class="token number">1</span> │ tom  │ 上海       │  <span class="token number">25</span> │ 技术部 │ <span class="token number">20000.00</span> │
│      <span class="token number">2</span> │ jack │ 上海       │  <span class="token number">26</span> │ 人事部 │ <span class="token number">10000.00</span> │
└────────┴──────┴────────────┴─────┴────────┴──────────┘
┌─emp_id─┬─name─┬─work_place─┬─age─┬─depart───┬───salary─┐
│      <span class="token number">4</span> │ tony │ 杭州       │  <span class="token number">28</span> │ 销售事部 │ <span class="token number">50000.00</span> │
└────────┴──────┴────────────┴─────┴──────────┴──────────┘
</code></pre> 
<p>可以看出，新插入的数据新生成了一个数据块，并没有与原来的分区数据在一起，我们可以执行<strong>optimize</strong>命令，执行合并操作</p> 
<pre class=" language-sql"><code class="prism  language-sql"><span class="token comment">-- 执行合并操作</span>
cdh04 :<span class="token punctuation">)</span> <span class="token keyword">OPTIMIZE</span> <span class="token keyword">TABLE</span> emp_mergetree <span class="token keyword">PARTITION</span> <span class="token string">'北京'</span><span class="token punctuation">;</span>
<span class="token comment">-- 再次执行查询</span>
cdh04 :<span class="token punctuation">)</span> <span class="token keyword">select</span> <span class="token operator">*</span> <span class="token keyword">from</span> emp_mergetree<span class="token punctuation">;</span>                  
<span class="token keyword">SELECT</span> <span class="token operator">*</span>
<span class="token keyword">FROM</span> emp_mergetree

┌─emp_id─┬─name─┬─work_place─┬─age─┬─depart─┬───salary─┐
│      <span class="token number">1</span> │ tom  │ 上海       │  <span class="token number">25</span> │ 技术部 │ <span class="token number">20000.00</span> │
│      <span class="token number">2</span> │ jack │ 上海       │  <span class="token number">26</span> │ 人事部 │ <span class="token number">10000.00</span> │
└────────┴──────┴────────────┴─────┴────────┴──────────┘
┌─emp_id─┬─name──┬─work_place─┬─age─┬─depart───┬───salary─┐
│      <span class="token number">3</span> │ bob   │ 北京       │  <span class="token number">33</span> │ 财务部   │ <span class="token number">50000.00</span> │
│      <span class="token number">5</span> │ robin │ 北京       │  <span class="token number">35</span> │ 财务部   │ <span class="token number">50000.00</span> │
│      <span class="token number">6</span> │ lilei │ 北京       │  <span class="token number">38</span> │ 销售事部 │ <span class="token number">50000.00</span> │
└────────┴───────┴────────────┴─────┴──────────┴──────────┘
┌─emp_id─┬─name─┬─work_place─┬─age─┬─depart───┬───salary─┐
│      <span class="token number">4</span> │ tony │ 杭州       │  <span class="token number">28</span> │ 销售事部 │ <span class="token number">50000.00</span> │
└────────┴──────┴────────────┴─────┴──────────┴──────────┘

</code></pre> 
<p>执行上面的合并操作之后，会新生成一个该分区的文件夹，原理的分区文件夹不变。</p> 
<ul><li>在MergeTree中主键并不用于去重，而是用于索引，加快查询速度</li></ul> 
<pre class=" language-sql"><code class="prism  language-sql"><span class="token comment">-- 插入一条相同主键的数据</span>
 <span class="token keyword">INSERT</span> <span class="token keyword">INTO</span> emp_mergetree
<span class="token keyword">VALUES</span> <span class="token punctuation">(</span><span class="token number">1</span><span class="token punctuation">,</span><span class="token string">'sam'</span><span class="token punctuation">,</span><span class="token string">'杭州'</span><span class="token punctuation">,</span><span class="token number">35</span><span class="token punctuation">,</span><span class="token string">'财务部'</span><span class="token punctuation">,</span><span class="token number">50000</span><span class="token punctuation">)</span><span class="token punctuation">;</span>
<span class="token comment">-- 会发现该条数据可以插入，由此可知，并不会对主键进行去重</span>
</code></pre> 
<h3><a id="ReplacingMergeTree_434"></a>ReplacingMergeTree表引擎</h3> 
<p>上文提到<strong>MergeTree</strong>表引擎无法对相同主键的数据进行去重，ClickHouse提供了ReplacingMergeTree引擎，可以针对相同主键的数据进行去重，它能够在合并分区时删除重复的数据。值得注意的是，<strong>ReplacingMergeTree</strong>只是在一定程度上解决了数据重复问题，但是并不能完全保障数据不重复。</p> 
<h4><a id="_438"></a>建表语法</h4> 
<pre class=" language-sql"><code class="prism  language-sql"><span class="token keyword">CREATE</span> <span class="token keyword">TABLE</span> <span class="token punctuation">[</span><span class="token keyword">IF</span> <span class="token operator">NOT</span> <span class="token keyword">EXISTS</span><span class="token punctuation">]</span> <span class="token punctuation">[</span>db<span class="token punctuation">.</span><span class="token punctuation">]</span>table_name <span class="token punctuation">[</span><span class="token keyword">ON</span> CLUSTER cluster<span class="token punctuation">]</span>
<span class="token punctuation">(</span>
    name1 <span class="token punctuation">[</span>type1<span class="token punctuation">]</span> <span class="token punctuation">[</span><span class="token keyword">DEFAULT</span><span class="token operator">|</span>MATERIALIZED<span class="token operator">|</span>ALIAS expr1<span class="token punctuation">]</span><span class="token punctuation">,</span>
    name2 <span class="token punctuation">[</span>type2<span class="token punctuation">]</span> <span class="token punctuation">[</span><span class="token keyword">DEFAULT</span><span class="token operator">|</span>MATERIALIZED<span class="token operator">|</span>ALIAS expr2<span class="token punctuation">]</span><span class="token punctuation">,</span>
    <span class="token punctuation">.</span><span class="token punctuation">.</span><span class="token punctuation">.</span>
<span class="token punctuation">)</span> <span class="token keyword">ENGINE</span> <span class="token operator">=</span> ReplacingMergeTree<span class="token punctuation">(</span><span class="token punctuation">[</span>ver<span class="token punctuation">]</span><span class="token punctuation">)</span>
<span class="token punctuation">[</span><span class="token keyword">PARTITION</span> <span class="token keyword">BY</span> expr<span class="token punctuation">]</span>
<span class="token punctuation">[</span><span class="token keyword">ORDER</span> <span class="token keyword">BY</span> expr<span class="token punctuation">]</span>
<span class="token punctuation">[</span><span class="token keyword">PRIMARY</span> <span class="token keyword">KEY</span> expr<span class="token punctuation">]</span>
<span class="token punctuation">[</span>SAMPLE <span class="token keyword">BY</span> expr<span class="token punctuation">]</span>
<span class="token punctuation">[</span>SETTINGS name<span class="token operator">=</span><span class="token keyword">value</span><span class="token punctuation">,</span> <span class="token punctuation">.</span><span class="token punctuation">.</span><span class="token punctuation">.</span><span class="token punctuation">]</span>
</code></pre> 
<ul><li>[ver]：可选参数，列的版本，可以是UInt、Date或者DateTime类型的字段作为版本号。该参数决定了数据去重的方式。</li><li>当没有指定[ver]参数时，保留最新的数据；如果指定了具体的值，保留最大的版本数据。</li></ul> 
<h4><a id="_457"></a>建表示例</h4> 
<pre class=" language-sql"><code class="prism  language-sql"><span class="token keyword">CREATE</span> <span class="token keyword">TABLE</span> emp_replacingmergetree <span class="token punctuation">(</span>
  emp_id UInt16 <span class="token keyword">COMMENT</span> <span class="token string">'员工id'</span><span class="token punctuation">,</span>
  name String <span class="token keyword">COMMENT</span> <span class="token string">'员工姓名'</span><span class="token punctuation">,</span>
  work_place String <span class="token keyword">COMMENT</span> <span class="token string">'工作地点'</span><span class="token punctuation">,</span>
  age UInt8 <span class="token keyword">COMMENT</span> <span class="token string">'员工年龄'</span><span class="token punctuation">,</span>
  depart String <span class="token keyword">COMMENT</span> <span class="token string">'部门'</span><span class="token punctuation">,</span>
  salary Decimal32<span class="token punctuation">(</span><span class="token number">2</span><span class="token punctuation">)</span> <span class="token keyword">COMMENT</span> <span class="token string">'工资'</span>
  <span class="token punctuation">)</span><span class="token keyword">ENGINE</span><span class="token operator">=</span>ReplacingMergeTree<span class="token punctuation">(</span><span class="token punctuation">)</span>
  <span class="token keyword">ORDER</span> <span class="token keyword">BY</span> emp_id
  <span class="token keyword">PRIMARY</span> <span class="token keyword">KEY</span> emp_id
  <span class="token keyword">PARTITION</span> <span class="token keyword">BY</span> work_place
  <span class="token punctuation">;</span>
 <span class="token comment">-- 插入数据 </span>
<span class="token keyword">INSERT</span> <span class="token keyword">INTO</span> emp_replacingmergetree
<span class="token keyword">VALUES</span> <span class="token punctuation">(</span><span class="token number">1</span><span class="token punctuation">,</span><span class="token string">'tom'</span><span class="token punctuation">,</span><span class="token string">'上海'</span><span class="token punctuation">,</span><span class="token number">25</span><span class="token punctuation">,</span><span class="token string">'技术部'</span><span class="token punctuation">,</span><span class="token number">20000</span><span class="token punctuation">)</span><span class="token punctuation">,</span><span class="token punctuation">(</span><span class="token number">2</span><span class="token punctuation">,</span><span class="token string">'jack'</span><span class="token punctuation">,</span><span class="token string">'上海'</span><span class="token punctuation">,</span><span class="token number">26</span><span class="token punctuation">,</span><span class="token string">'人事部'</span><span class="token punctuation">,</span><span class="token number">10000</span><span class="token punctuation">)</span><span class="token punctuation">;</span>
<span class="token keyword">INSERT</span> <span class="token keyword">INTO</span> emp_replacingmergetree
<span class="token keyword">VALUES</span> <span class="token punctuation">(</span><span class="token number">3</span><span class="token punctuation">,</span><span class="token string">'bob'</span><span class="token punctuation">,</span><span class="token string">'北京'</span><span class="token punctuation">,</span><span class="token number">33</span><span class="token punctuation">,</span><span class="token string">'财务部'</span><span class="token punctuation">,</span><span class="token number">50000</span><span class="token punctuation">)</span><span class="token punctuation">,</span><span class="token punctuation">(</span><span class="token number">4</span><span class="token punctuation">,</span><span class="token string">'tony'</span><span class="token punctuation">,</span><span class="token string">'杭州'</span><span class="token punctuation">,</span><span class="token number">28</span><span class="token punctuation">,</span><span class="token string">'销售事部'</span><span class="token punctuation">,</span><span class="token number">50000</span><span class="token punctuation">)</span><span class="token punctuation">;</span> 
</code></pre> 
<h4><a id="_479"></a>注意点</h4> 
<p>当我们再次向该表插入具有相同主键的数据时，观察查询数据的变化</p> 
<pre class=" language-sql"><code class="prism  language-sql"><span class="token keyword">INSERT</span> <span class="token keyword">INTO</span> emp_replacingmergetree
<span class="token keyword">VALUES</span> <span class="token punctuation">(</span><span class="token number">1</span><span class="token punctuation">,</span><span class="token string">'tom'</span><span class="token punctuation">,</span><span class="token string">'上海'</span><span class="token punctuation">,</span><span class="token number">25</span><span class="token punctuation">,</span><span class="token string">'技术部'</span><span class="token punctuation">,</span><span class="token number">50000</span><span class="token punctuation">)</span><span class="token punctuation">;</span>
<span class="token comment">-- 查询数据，由于没有进行合并，所以存在主键重复的数据</span>
cdh04 :<span class="token punctuation">)</span> <span class="token keyword">select</span> <span class="token operator">*</span> <span class="token keyword">from</span> emp_replacingmergetree<span class="token punctuation">;</span>                                        

<span class="token keyword">SELECT</span> <span class="token operator">*</span>
<span class="token keyword">FROM</span> emp_replacingmergetree

┌─emp_id─┬─name─┬─work_place─┬─age─┬─depart─┬───salary─┐
│      <span class="token number">1</span> │ tom  │ 上海       │  <span class="token number">25</span> │ 技术部 │ <span class="token number">20000.00</span> │
│      <span class="token number">2</span> │ jack │ 上海       │  <span class="token number">26</span> │ 人事部 │ <span class="token number">10000.00</span> │
└────────┴──────┴────────────┴─────┴────────┴──────────┘
┌─emp_id─┬─name─┬─work_place─┬─age─┬─depart─┬───salary─┐
│      <span class="token number">3</span> │ bob  │ 北京       │  <span class="token number">33</span> │ 财务部 │ <span class="token number">50000.00</span> │
└────────┴──────┴────────────┴─────┴────────┴──────────┘
┌─emp_id─┬─name─┬─work_place─┬─age─┬─depart───┬───salary─┐
│      <span class="token number">4</span> │ tony │ 杭州       │  <span class="token number">28</span> │ 销售事部 │ <span class="token number">50000.00</span> │
└────────┴──────┴────────────┴─────┴──────────┴──────────┘
┌─emp_id─┬─name─┬─work_place─┬─age─┬─depart─┬───salary─┐
│      <span class="token number">1</span> │ tom  │ 上海       │  <span class="token number">25</span> │ 技术部 │ <span class="token number">50000.00</span> │
└────────┴──────┴────────────┴─────┴────────┴──────────┘
<span class="token comment">-- 执行合并操作</span>
<span class="token keyword">optimize</span> <span class="token keyword">table</span> emp_replacingmergetree final<span class="token punctuation">;</span>
<span class="token comment">-- 再次查询，相同主键的数据，保留最近插入的数据，旧的数据被清除</span>
cdh04 :<span class="token punctuation">)</span> <span class="token keyword">select</span> <span class="token operator">*</span> <span class="token keyword">from</span> emp_replacingmergetree<span class="token punctuation">;</span>       

<span class="token keyword">SELECT</span> <span class="token operator">*</span>
<span class="token keyword">FROM</span> emp_replacingmergetree

┌─emp_id─┬─name─┬─work_place─┬─age─┬─depart─┬───salary─┐
│      <span class="token number">1</span> │ tom  │ 上海       │  <span class="token number">25</span> │ 技术部 │ <span class="token number">50000.00</span> │
│      <span class="token number">2</span> │ jack │ 上海       │  <span class="token number">26</span> │ 人事部 │ <span class="token number">10000.00</span> │
└────────┴──────┴────────────┴─────┴────────┴──────────┘
┌─emp_id─┬─name─┬─work_place─┬─age─┬─depart───┬───salary─┐
│      <span class="token number">4</span> │ tony │ 杭州       │  <span class="token number">28</span> │ 销售事部 │ <span class="token number">50000.00</span> │
└────────┴──────┴────────────┴─────┴──────────┴──────────┘
┌─emp_id─┬─name─┬─work_place─┬─age─┬─depart─┬───salary─┐
│      <span class="token number">3</span> │ bob  │ 北京       │  <span class="token number">33</span> │ 财务部 │ <span class="token number">50000.00</span> │
└────────┴──────┴────────────┴─────┴────────┴──────────┘
</code></pre> 
<p>从上面的示例中可以看出，ReplacingMergeTree是支持对数据去重的，那么是根据什么进行去重呢？答案是：<strong>ReplacingMergeTree在去除重复数据时，是以ORDERBY排序键为基准的，而不是PRIMARY KEY</strong>。我们在看一个示例：</p> 
<pre class=" language-sql"><code class="prism  language-sql"><span class="token keyword">CREATE</span> <span class="token keyword">TABLE</span> emp_replacingmergetree1 <span class="token punctuation">(</span>
  emp_id UInt16 <span class="token keyword">COMMENT</span> <span class="token string">'员工id'</span><span class="token punctuation">,</span>
  name String <span class="token keyword">COMMENT</span> <span class="token string">'员工姓名'</span><span class="token punctuation">,</span>
  work_place String <span class="token keyword">COMMENT</span> <span class="token string">'工作地点'</span><span class="token punctuation">,</span>
  age UInt8 <span class="token keyword">COMMENT</span> <span class="token string">'员工年龄'</span><span class="token punctuation">,</span>
  depart String <span class="token keyword">COMMENT</span> <span class="token string">'部门'</span><span class="token punctuation">,</span>
  salary Decimal32<span class="token punctuation">(</span><span class="token number">2</span><span class="token punctuation">)</span> <span class="token keyword">COMMENT</span> <span class="token string">'工资'</span>
  <span class="token punctuation">)</span><span class="token keyword">ENGINE</span><span class="token operator">=</span>ReplacingMergeTree<span class="token punctuation">(</span><span class="token punctuation">)</span>
  <span class="token keyword">ORDER</span> <span class="token keyword">BY</span> <span class="token punctuation">(</span>emp_id<span class="token punctuation">,</span>name<span class="token punctuation">)</span> <span class="token comment">-- 注意排序key是两个字段</span>
  <span class="token keyword">PRIMARY</span> <span class="token keyword">KEY</span> emp_id     <span class="token comment">-- 主键是一个字段</span>
  <span class="token keyword">PARTITION</span> <span class="token keyword">BY</span> work_place
  <span class="token punctuation">;</span>
 <span class="token comment">-- 插入数据 </span>
<span class="token keyword">INSERT</span> <span class="token keyword">INTO</span> emp_replacingmergetree1
<span class="token keyword">VALUES</span> <span class="token punctuation">(</span><span class="token number">1</span><span class="token punctuation">,</span><span class="token string">'tom'</span><span class="token punctuation">,</span><span class="token string">'上海'</span><span class="token punctuation">,</span><span class="token number">25</span><span class="token punctuation">,</span><span class="token string">'技术部'</span><span class="token punctuation">,</span><span class="token number">20000</span><span class="token punctuation">)</span><span class="token punctuation">,</span><span class="token punctuation">(</span><span class="token number">2</span><span class="token punctuation">,</span><span class="token string">'jack'</span><span class="token punctuation">,</span><span class="token string">'上海'</span><span class="token punctuation">,</span><span class="token number">26</span><span class="token punctuation">,</span><span class="token string">'人事部'</span><span class="token punctuation">,</span><span class="token number">10000</span><span class="token punctuation">)</span><span class="token punctuation">;</span>
<span class="token keyword">INSERT</span> <span class="token keyword">INTO</span> emp_replacingmergetree1
<span class="token keyword">VALUES</span> <span class="token punctuation">(</span><span class="token number">3</span><span class="token punctuation">,</span><span class="token string">'bob'</span><span class="token punctuation">,</span><span class="token string">'北京'</span><span class="token punctuation">,</span><span class="token number">33</span><span class="token punctuation">,</span><span class="token string">'财务部'</span><span class="token punctuation">,</span><span class="token number">50000</span><span class="token punctuation">)</span><span class="token punctuation">,</span><span class="token punctuation">(</span><span class="token number">4</span><span class="token punctuation">,</span><span class="token string">'tony'</span><span class="token punctuation">,</span><span class="token string">'杭州'</span><span class="token punctuation">,</span><span class="token number">28</span><span class="token punctuation">,</span><span class="token string">'销售事部'</span><span class="token punctuation">,</span><span class="token number">50000</span><span class="token punctuation">)</span><span class="token punctuation">;</span> 
</code></pre> 
<p>再次向该表中插入相同emp_id和name的数据，并执行合并操作，再观察数据</p> 
<pre class=" language-sql"><code class="prism  language-sql"><span class="token comment">-- 插入数据</span>
<span class="token keyword">INSERT</span> <span class="token keyword">INTO</span> emp_replacingmergetree1
<span class="token keyword">VALUES</span> <span class="token punctuation">(</span><span class="token number">1</span><span class="token punctuation">,</span><span class="token string">'tom'</span><span class="token punctuation">,</span><span class="token string">'上海'</span><span class="token punctuation">,</span><span class="token number">25</span><span class="token punctuation">,</span><span class="token string">'技术部'</span><span class="token punctuation">,</span><span class="token number">50000</span><span class="token punctuation">)</span><span class="token punctuation">,</span><span class="token punctuation">(</span><span class="token number">1</span><span class="token punctuation">,</span><span class="token string">'sam'</span><span class="token punctuation">,</span><span class="token string">'上海'</span><span class="token punctuation">,</span><span class="token number">25</span><span class="token punctuation">,</span><span class="token string">'技术部'</span><span class="token punctuation">,</span><span class="token number">20000</span><span class="token punctuation">)</span><span class="token punctuation">;</span>
<span class="token comment">-- 执行合并操作</span>
<span class="token keyword">optimize</span> <span class="token keyword">table</span> emp_replacingmergetree1 final<span class="token punctuation">;</span>
<span class="token comment">-- 再次查询，可见相同的emp_id和name数据被去重，而形同的主键emp_id不会去重</span>
<span class="token comment">-- ReplacingMergeTree在去除重复数据时，是以ORDERBY排序键为基准的，而不是PRIMARY KEY</span>
cdh04 :<span class="token punctuation">)</span> <span class="token keyword">select</span> <span class="token operator">*</span> <span class="token keyword">from</span> emp_replacingmergetree1<span class="token punctuation">;</span>                                                                           
<span class="token keyword">SELECT</span> <span class="token operator">*</span>
<span class="token keyword">FROM</span> emp_replacingmergetree1

┌─emp_id─┬─name─┬─work_place─┬─age─┬─depart─┬───salary─┐
│      <span class="token number">3</span> │ bob  │ 北京       │  <span class="token number">33</span> │ 财务部 │ <span class="token number">50000.00</span> │
└────────┴──────┴────────────┴─────┴────────┴──────────┘
┌─emp_id─┬─name─┬─work_place─┬─age─┬─depart─┬───salary─┐
│      <span class="token number">1</span> │ sam  │ 上海       │  <span class="token number">25</span> │ 技术部 │ <span class="token number">20000.00</span> │
│      <span class="token number">1</span> │ tom  │ 上海       │  <span class="token number">25</span> │ 技术部 │ <span class="token number">50000.00</span> │
│      <span class="token number">2</span> │ jack │ 上海       │  <span class="token number">26</span> │ 人事部 │ <span class="token number">10000.00</span> │
└────────┴──────┴────────────┴─────┴────────┴──────────┘
┌─emp_id─┬─name─┬─work_place─┬─age─┬─depart───┬───salary─┐
│      <span class="token number">4</span> │ tony │ 杭州       │  <span class="token number">28</span> │ 销售事部 │ <span class="token number">50000.00</span> │
└────────┴──────┴────────────┴─────┴──────────┴──────────┘
</code></pre> 
<p>至此，我们知道了ReplacingMergeTree是支持去重的，并且是按照<strong>ORDERBY排序键</strong>为基准进行去重的。细心的你会发现，上面的重复数据是在一个分区内的，那么如果重复的数据不在一个分区内，会发生什么现象呢？我们再次向上面的<strong>emp_replacingmergetree1</strong>表插入不同分区的重复数据</p> 
<pre class=" language-sql"><code class="prism  language-sql"><span class="token comment">-- 插入数据</span>
<span class="token keyword">INSERT</span> <span class="token keyword">INTO</span> emp_replacingmergetree1
<span class="token keyword">VALUES</span> <span class="token punctuation">(</span><span class="token number">1</span><span class="token punctuation">,</span><span class="token string">'tom'</span><span class="token punctuation">,</span><span class="token string">'北京'</span><span class="token punctuation">,</span><span class="token number">26</span><span class="token punctuation">,</span><span class="token string">'技术部'</span><span class="token punctuation">,</span><span class="token number">10000</span><span class="token punctuation">)</span><span class="token punctuation">;</span>
<span class="token comment">-- 执行合并操作</span>
<span class="token keyword">optimize</span> <span class="token keyword">table</span> emp_replacingmergetree1 final<span class="token punctuation">;</span>
<span class="token comment">-- 再次查询</span>
<span class="token comment">-- 发现  1 │ tom  │ 北京       │  26 │ 技术部 │ 10000.00</span>
<span class="token comment">-- 与    1 │ tom  │ 上海       │  25 │ 技术部 │ 50000.00</span>
<span class="token comment">-- 数据重复，因为这两行数据不在同一个分区内</span>
<span class="token comment">-- 这是因为ReplacingMergeTree是以分区为单位删除重复数据的。</span>
<span class="token comment">-- 只有在相同的数据分区内重复的数据才可以被删除，而不同数据分区之间的重复数据依然不能被剔除</span>
cdh04 :<span class="token punctuation">)</span> <span class="token keyword">select</span> <span class="token operator">*</span> <span class="token keyword">from</span> emp_replacingmergetree1<span class="token punctuation">;</span>       

<span class="token keyword">SELECT</span> <span class="token operator">*</span>
<span class="token keyword">FROM</span> emp_replacingmergetree1

┌─emp_id─┬─name─┬─work_place─┬─age─┬─depart─┬───salary─┐
│      <span class="token number">1</span> │ tom  │ 北京       │  <span class="token number">26</span> │ 技术部 │ <span class="token number">10000.00</span> │
│      <span class="token number">3</span> │ bob  │ 北京       │  <span class="token number">33</span> │ 财务部 │ <span class="token number">50000.00</span> │
└────────┴──────┴────────────┴─────┴────────┴──────────┘
┌─emp_id─┬─name─┬─work_place─┬─age─┬─depart─┬───salary─┐
│      <span class="token number">1</span> │ sam  │ 上海       │  <span class="token number">25</span> │ 技术部 │ <span class="token number">20000.00</span> │
│      <span class="token number">1</span> │ tom  │ 上海       │  <span class="token number">25</span> │ 技术部 │ <span class="token number">50000.00</span> │
│      <span class="token number">2</span> │ jack │ 上海       │  <span class="token number">26</span> │ 人事部 │ <span class="token number">10000.00</span> │
└────────┴──────┴────────────┴─────┴────────┴──────────┘
┌─emp_id─┬─name─┬─work_place─┬─age─┬─depart───┬───salary─┐
│      <span class="token number">4</span> │ tony │ 杭州       │  <span class="token number">28</span> │ 销售事部 │ <span class="token number">50000.00</span> │
└────────┴──────┴────────────┴─────┴──────────┴──────────┘
</code></pre> 
<h4><a id="_607"></a>总结</h4> 
<ul><li><strong>如何判断数据重复</strong></li></ul> 
<p>ReplacingMergeTree在去除重复数据时，是以ORDERBY排序键为基准的，而不是PRIMARY KEY。</p> 
<ul><li><strong>何时删除重复数据</strong></li></ul> 
<p>在执行分区合并时，会触发删除重复数据。optimize的合并操作是在后台执行的，无法预测具体执行时间点，除非是手动执行。</p> 
<ul><li><strong>不同分区的重复数据不会被去重</strong></li></ul> 
<p>ReplacingMergeTree是以分区为单位删除重复数据的。只有在相同的数据分区内重复的数据才可以被删除，而不同数据分区之间的重复数据依然不能被剔除。</p> 
<ul><li><strong>数据去重的策略是什么</strong></li></ul> 
<p>如果没有设置**[ver]版本号**，则保留同一组重复数据中的最新插入的数据； 如果设置了**[ver]版本号**，则保留同一组重复数据中<strong>ver字段取值最大的那一行</strong>。</p> 
<ul><li><strong>optimize命令使用</strong></li></ul> 
<p>一般在数据量比较大的情况，尽量不要使用该命令。因为在海量数据场景下，执行optimize要消耗大量时间</p> 
<h3><a id="SummingMergeTree_629"></a>SummingMergeTree表引擎</h3> 
<p>该引擎继承了MergeTree引擎，当合并 <code>SummingMergeTree</code> 表的数据片段时，ClickHouse 会把所有具有相同主键的行合并为一行，该行包含了被合并的行中具有数值数据类型的列的汇总值，即如果存在重复的数据，会对对这些重复的数据进行合并成一条数据，类似于group by的效果。</p> 
<p>推荐将该引擎和 <code>MergeTree</code> 一起使用。例如，将完整的数据存储在 <code>MergeTree</code> 表中，并且使用 <code>SummingMergeTree</code> 来存储聚合数据。这种方法可以避免因为使用不正确的主键组合方式而丢失数据。</p> 
<p>如果用户只需要查询数据的汇总结果，不关心明细数据，并且数据的汇总条件是预先明确的，即<strong>GROUP BY的分组字段是确定的</strong>，可以使用该表引擎。</p> 
<h4><a id="_637"></a>建表语法</h4> 
<pre class=" language-sql"><code class="prism  language-sql"><span class="token keyword">CREATE</span> <span class="token keyword">TABLE</span> <span class="token punctuation">[</span><span class="token keyword">IF</span> <span class="token operator">NOT</span> <span class="token keyword">EXISTS</span><span class="token punctuation">]</span> <span class="token punctuation">[</span>db<span class="token punctuation">.</span><span class="token punctuation">]</span>table_name <span class="token punctuation">[</span><span class="token keyword">ON</span> CLUSTER cluster<span class="token punctuation">]</span>
<span class="token punctuation">(</span>
    name1 <span class="token punctuation">[</span>type1<span class="token punctuation">]</span> <span class="token punctuation">[</span><span class="token keyword">DEFAULT</span><span class="token operator">|</span>MATERIALIZED<span class="token operator">|</span>ALIAS expr1<span class="token punctuation">]</span><span class="token punctuation">,</span>
    name2 <span class="token punctuation">[</span>type2<span class="token punctuation">]</span> <span class="token punctuation">[</span><span class="token keyword">DEFAULT</span><span class="token operator">|</span>MATERIALIZED<span class="token operator">|</span>ALIAS expr2<span class="token punctuation">]</span><span class="token punctuation">,</span>
    <span class="token punctuation">.</span><span class="token punctuation">.</span><span class="token punctuation">.</span>
<span class="token punctuation">)</span> <span class="token keyword">ENGINE</span> <span class="token operator">=</span> SummingMergeTree<span class="token punctuation">(</span><span class="token punctuation">[</span><span class="token keyword">columns</span><span class="token punctuation">]</span><span class="token punctuation">)</span> <span class="token comment">-- 指定合并汇总字段</span>
<span class="token punctuation">[</span><span class="token keyword">PARTITION</span> <span class="token keyword">BY</span> expr<span class="token punctuation">]</span>
<span class="token punctuation">[</span><span class="token keyword">ORDER</span> <span class="token keyword">BY</span> expr<span class="token punctuation">]</span>
<span class="token punctuation">[</span>SAMPLE <span class="token keyword">BY</span> expr<span class="token punctuation">]</span>
<span class="token punctuation">[</span>SETTINGS name<span class="token operator">=</span><span class="token keyword">value</span><span class="token punctuation">,</span> <span class="token punctuation">.</span><span class="token punctuation">.</span><span class="token punctuation">.</span><span class="token punctuation">]</span>
</code></pre> 
<h4><a id="_652"></a>建表示例</h4> 
<pre class=" language-sql"><code class="prism  language-sql"><span class="token keyword">CREATE</span> <span class="token keyword">TABLE</span> emp_summingmergetree <span class="token punctuation">(</span>
  emp_id UInt16 <span class="token keyword">COMMENT</span> <span class="token string">'员工id'</span><span class="token punctuation">,</span>
  name String <span class="token keyword">COMMENT</span> <span class="token string">'员工姓名'</span><span class="token punctuation">,</span>
  work_place String <span class="token keyword">COMMENT</span> <span class="token string">'工作地点'</span><span class="token punctuation">,</span>
  age UInt8 <span class="token keyword">COMMENT</span> <span class="token string">'员工年龄'</span><span class="token punctuation">,</span>
  depart String <span class="token keyword">COMMENT</span> <span class="token string">'部门'</span><span class="token punctuation">,</span>
  salary Decimal32<span class="token punctuation">(</span><span class="token number">2</span><span class="token punctuation">)</span> <span class="token keyword">COMMENT</span> <span class="token string">'工资'</span>
  <span class="token punctuation">)</span><span class="token keyword">ENGINE</span><span class="token operator">=</span>SummingMergeTree<span class="token punctuation">(</span>salary<span class="token punctuation">)</span>
  <span class="token keyword">ORDER</span> <span class="token keyword">BY</span> <span class="token punctuation">(</span>emp_id<span class="token punctuation">,</span>name<span class="token punctuation">)</span> <span class="token comment">-- 注意排序key是两个字段</span>
  <span class="token keyword">PRIMARY</span> <span class="token keyword">KEY</span> emp_id     <span class="token comment">-- 主键是一个字段</span>
  <span class="token keyword">PARTITION</span> <span class="token keyword">BY</span> work_place
  <span class="token punctuation">;</span>
 <span class="token comment">-- 插入数据 </span>
<span class="token keyword">INSERT</span> <span class="token keyword">INTO</span> emp_summingmergetree
<span class="token keyword">VALUES</span> <span class="token punctuation">(</span><span class="token number">1</span><span class="token punctuation">,</span><span class="token string">'tom'</span><span class="token punctuation">,</span><span class="token string">'上海'</span><span class="token punctuation">,</span><span class="token number">25</span><span class="token punctuation">,</span><span class="token string">'技术部'</span><span class="token punctuation">,</span><span class="token number">20000</span><span class="token punctuation">)</span><span class="token punctuation">,</span><span class="token punctuation">(</span><span class="token number">2</span><span class="token punctuation">,</span><span class="token string">'jack'</span><span class="token punctuation">,</span><span class="token string">'上海'</span><span class="token punctuation">,</span><span class="token number">26</span><span class="token punctuation">,</span><span class="token string">'人事部'</span><span class="token punctuation">,</span><span class="token number">10000</span><span class="token punctuation">)</span><span class="token punctuation">;</span>
<span class="token keyword">INSERT</span> <span class="token keyword">INTO</span> emp_summingmergetree
<span class="token keyword">VALUES</span> <span class="token punctuation">(</span><span class="token number">3</span><span class="token punctuation">,</span><span class="token string">'bob'</span><span class="token punctuation">,</span><span class="token string">'北京'</span><span class="token punctuation">,</span><span class="token number">33</span><span class="token punctuation">,</span><span class="token string">'财务部'</span><span class="token punctuation">,</span><span class="token number">50000</span><span class="token punctuation">)</span><span class="token punctuation">,</span><span class="token punctuation">(</span><span class="token number">4</span><span class="token punctuation">,</span><span class="token string">'tony'</span><span class="token punctuation">,</span><span class="token string">'杭州'</span><span class="token punctuation">,</span><span class="token number">28</span><span class="token punctuation">,</span><span class="token string">'销售事部'</span><span class="token punctuation">,</span><span class="token number">50000</span><span class="token punctuation">)</span><span class="token punctuation">;</span> 

</code></pre> 
<p>当我们再次插入具有相同emp_id,name的数据时，观察结果</p> 
<pre class=" language-sql"><code class="prism  language-sql"><span class="token keyword">INSERT</span> <span class="token keyword">INTO</span> emp_summingmergetree
<span class="token keyword">VALUES</span> <span class="token punctuation">(</span><span class="token number">1</span><span class="token punctuation">,</span><span class="token string">'tom'</span><span class="token punctuation">,</span><span class="token string">'上海'</span><span class="token punctuation">,</span><span class="token number">25</span><span class="token punctuation">,</span><span class="token string">'信息部'</span><span class="token punctuation">,</span><span class="token number">10000</span><span class="token punctuation">)</span><span class="token punctuation">,</span><span class="token punctuation">(</span><span class="token number">1</span><span class="token punctuation">,</span><span class="token string">'tom'</span><span class="token punctuation">,</span><span class="token string">'北京'</span><span class="token punctuation">,</span><span class="token number">26</span><span class="token punctuation">,</span><span class="token string">'人事部'</span><span class="token punctuation">,</span><span class="token number">10000</span><span class="token punctuation">)</span><span class="token punctuation">;</span>
cdh04 :<span class="token punctuation">)</span> <span class="token keyword">select</span> <span class="token operator">*</span> <span class="token keyword">from</span> emp_summingmergetree<span class="token punctuation">;</span>
<span class="token comment">-- 查询</span>
<span class="token keyword">SELECT</span> <span class="token operator">*</span>
<span class="token keyword">FROM</span> emp_summingmergetree

┌─emp_id─┬─name─┬─work_place─┬─age─┬─depart─┬───salary─┐
│      <span class="token number">3</span> │ bob  │ 北京       │  <span class="token number">33</span> │ 财务部 │ <span class="token number">50000.00</span> │
└────────┴──────┴────────────┴─────┴────────┴──────────┘
┌─emp_id─┬─name─┬─work_place─┬─age─┬─depart─┬───salary─┐
│      <span class="token number">1</span> │ tom  │ 上海       │  <span class="token number">25</span> │ 技术部 │ <span class="token number">20000.00</span> │
│      <span class="token number">2</span> │ jack │ 上海       │  <span class="token number">26</span> │ 人事部 │ <span class="token number">10000.00</span> │
└────────┴──────┴────────────┴─────┴────────┴──────────┘
┌─emp_id─┬─name─┬─work_place─┬─age─┬─depart───┬───salary─┐
│      <span class="token number">4</span> │ tony │ 杭州       │  <span class="token number">28</span> │ 销售事部 │ <span class="token number">50000.00</span> │
└────────┴──────┴────────────┴─────┴──────────┴──────────┘
┌─emp_id─┬─name─┬─work_place─┬─age─┬─depart─┬───salary─┐
│      <span class="token number">1</span> │ tom  │ 北京       │  <span class="token number">26</span> │ 人事部 │ <span class="token number">10000.00</span> │
└────────┴──────┴────────────┴─────┴────────┴──────────┘
┌─emp_id─┬─name─┬─work_place─┬─age─┬─depart─┬───salary─┐
│      <span class="token number">1</span> │ tom  │ 上海       │  <span class="token number">25</span> │ 信息部 │ <span class="token number">10000.00</span> │
└────────┴──────┴────────────┴─────┴────────┴──────────┘
<span class="token comment">-- 执行合并操作</span>
<span class="token keyword">optimize</span> <span class="token keyword">table</span> emp_summingmergetree final<span class="token punctuation">;</span>
cdh04 :<span class="token punctuation">)</span> <span class="token keyword">select</span> <span class="token operator">*</span> <span class="token keyword">from</span> emp_summingmergetree<span class="token punctuation">;</span>       
<span class="token comment">-- 再次查询，新插入的数据 1 │ tom  │ 上海       │  25 │ 信息部 │ 10000.00 </span>
<span class="token comment">-- 原来的数据 ：        1 │ tom  │ 上海       │  25 │ 技术部 │ 20000.00</span>
<span class="token comment">-- 这两行数据合并成：    1 │ tom  │ 上海       │  25 │ 技术部 │ 30000.00</span>
<span class="token keyword">SELECT</span> <span class="token operator">*</span>
<span class="token keyword">FROM</span> emp_summingmergetree

┌─emp_id─┬─name─┬─work_place─┬─age─┬─depart─┬───salary─┐
│      <span class="token number">1</span> │ tom  │ 北京       │  <span class="token number">26</span> │ 人事部 │ <span class="token number">10000.00</span> │
│      <span class="token number">3</span> │ bob  │ 北京       │  <span class="token number">33</span> │ 财务部 │ <span class="token number">50000.00</span> │
└────────┴──────┴────────────┴─────┴────────┴──────────┘
┌─emp_id─┬─name─┬─work_place─┬─age─┬─depart─┬───salary─┐
│      <span class="token number">1</span> │ tom  │ 上海       │  <span class="token number">25</span> │ 技术部 │ <span class="token number">30000.00</span> │
│      <span class="token number">2</span> │ jack │ 上海       │  <span class="token number">26</span> │ 人事部 │ <span class="token number">10000.00</span> │
└────────┴──────┴────────────┴─────┴────────┴──────────┘
┌─emp_id─┬─name─┬─work_place─┬─age─┬─depart───┬───salary─┐
│      <span class="token number">4</span> │ tony │ 杭州       │  <span class="token number">28</span> │ 销售事部 │ <span class="token number">50000.00</span> │
└────────┴──────┴────────────┴─────┴──────────┴──────────┘
</code></pre> 
<h4><a id="_723"></a>注意点</h4> 
<p>要保证<strong>PRIMARY KEY expr</strong>指定的主键是<strong>ORDER BY expr</strong> 指定字段的前缀，比如</p> 
<pre class=" language-sql"><code class="prism  language-sql"><span class="token comment">-- 允许</span>
<span class="token keyword">ORDER</span> <span class="token keyword">BY</span> <span class="token punctuation">(</span>A<span class="token punctuation">,</span>B<span class="token punctuation">,</span>C<span class="token punctuation">)</span> 
<span class="token keyword">PRIMARY</span> <span class="token keyword">KEY</span> A  
<span class="token comment">-- 会报错</span>
<span class="token comment">--  DB::Exception: Primary key must be a prefix of the sorting key</span>
<span class="token keyword">ORDER</span> <span class="token keyword">BY</span> <span class="token punctuation">(</span>A<span class="token punctuation">,</span>B<span class="token punctuation">,</span>C<span class="token punctuation">)</span> 
<span class="token keyword">PRIMARY</span> <span class="token keyword">KEY</span> B
</code></pre> 
<p>这种强制约束保障了即便在两者定义不同的情况下，主键仍然是排序键的前缀，不会出现索引与数据顺序混乱的问题。</p> 
<h4><a id="_739"></a>总结</h4> 
<ul><li><strong>SummingMergeTree是根据什么对两条数据进行合并的</strong></li></ul> 
<p>用ORBER BY排序键作为聚合数据的条件Key。即如果排序key是相同的，则会合并成一条数据，并对指定的合并字段进行聚合。</p> 
<ul><li><strong>仅对分区内的相同排序key的数据行进行合并</strong></li></ul> 
<p>以数据分区为单位来聚合数据。当分区合并时，同一数据分区内聚合Key相同的数据会被合并汇总，而不同分区之间的数据则不会被汇总。</p> 
<ul><li><strong>如果没有指定聚合字段，会怎么聚合</strong></li></ul> 
<p>如果没有指定聚合字段，则会按照非主键的数值类型字段进行聚合</p> 
<ul><li><strong>对于非汇总字段的数据，该保留哪一条</strong></li></ul> 
<p>如果两行数据除了排序字段相同，其他的非聚合字段不相同，那么在聚合发生时，会保留最初的那条数据，新插入的数据对应的那个字段值会被舍弃</p> 
<pre class=" language-sql"><code class="prism  language-sql"><span class="token comment">-- 新插入的数据:        1 │ tom  │ 上海       │  25 │ 信息部 │ 10000.00 </span>
<span class="token comment">-- 最初的数据 ：        1 │ tom  │ 上海       │  25 │ 技术部 │ 20000.00</span>

<span class="token comment">-- 聚合合并的结果：      1 │ tom  │ 上海       │  25 │ 技术部 │ 30000.00</span>
</code></pre> 
<h3><a id="Aggregatingmergetree_764"></a>Aggregatingmergetree表引擎</h3> 
<p>该表引擎继承自MergeTree，可以使用 <code>AggregatingMergeTree</code> 表来做增量数据统计聚合。如果要按一组规则来合并减少行数，则使用 <code>AggregatingMergeTree</code> 是合适的。AggregatingMergeTree是通过预先定义的聚合函数计算数据并通过二进制的格式存入表内。</p> 
<p>与SummingMergeTree的区别在于：SummingMergeTree对非主键列进行sum聚合，而AggregatingMergeTree则可以指定各种聚合函数。</p> 
<h4><a id="_770"></a>建表语法</h4> 
<pre class=" language-sql"><code class="prism  language-sql"><span class="token keyword">CREATE</span> <span class="token keyword">TABLE</span> <span class="token punctuation">[</span><span class="token keyword">IF</span> <span class="token operator">NOT</span> <span class="token keyword">EXISTS</span><span class="token punctuation">]</span> <span class="token punctuation">[</span>db<span class="token punctuation">.</span><span class="token punctuation">]</span>table_name <span class="token punctuation">[</span><span class="token keyword">ON</span> CLUSTER cluster<span class="token punctuation">]</span>
<span class="token punctuation">(</span>
    name1 <span class="token punctuation">[</span>type1<span class="token punctuation">]</span> <span class="token punctuation">[</span><span class="token keyword">DEFAULT</span><span class="token operator">|</span>MATERIALIZED<span class="token operator">|</span>ALIAS expr1<span class="token punctuation">]</span><span class="token punctuation">,</span>
    name2 <span class="token punctuation">[</span>type2<span class="token punctuation">]</span> <span class="token punctuation">[</span><span class="token keyword">DEFAULT</span><span class="token operator">|</span>MATERIALIZED<span class="token operator">|</span>ALIAS expr2<span class="token punctuation">]</span><span class="token punctuation">,</span>
    <span class="token punctuation">.</span><span class="token punctuation">.</span><span class="token punctuation">.</span>
<span class="token punctuation">)</span> <span class="token keyword">ENGINE</span> <span class="token operator">=</span> AggregatingMergeTree<span class="token punctuation">(</span><span class="token punctuation">)</span>
<span class="token punctuation">[</span><span class="token keyword">PARTITION</span> <span class="token keyword">BY</span> expr<span class="token punctuation">]</span>
<span class="token punctuation">[</span><span class="token keyword">ORDER</span> <span class="token keyword">BY</span> expr<span class="token punctuation">]</span>
<span class="token punctuation">[</span>SAMPLE <span class="token keyword">BY</span> expr<span class="token punctuation">]</span>
<span class="token punctuation">[</span>SETTINGS name<span class="token operator">=</span><span class="token keyword">value</span><span class="token punctuation">,</span> <span class="token punctuation">.</span><span class="token punctuation">.</span><span class="token punctuation">.</span><span class="token punctuation">]</span>
</code></pre> 
<h4><a id="_785"></a>建表示例</h4> 
<pre class=" language-sql"><code class="prism  language-sql"><span class="token keyword">CREATE</span> <span class="token keyword">TABLE</span> emp_aggregatingmergeTree <span class="token punctuation">(</span>
  emp_id UInt16 <span class="token keyword">COMMENT</span> <span class="token string">'员工id'</span><span class="token punctuation">,</span>
  name String <span class="token keyword">COMMENT</span> <span class="token string">'员工姓名'</span><span class="token punctuation">,</span>
  work_place String <span class="token keyword">COMMENT</span> <span class="token string">'工作地点'</span><span class="token punctuation">,</span>
  age UInt8 <span class="token keyword">COMMENT</span> <span class="token string">'员工年龄'</span><span class="token punctuation">,</span>
  depart String <span class="token keyword">COMMENT</span> <span class="token string">'部门'</span><span class="token punctuation">,</span>
  salary  AggregateFunction<span class="token punctuation">(</span>sum<span class="token punctuation">,</span>Decimal32<span class="token punctuation">(</span><span class="token number">2</span><span class="token punctuation">)</span><span class="token punctuation">)</span> <span class="token keyword">COMMENT</span> <span class="token string">'工资'</span>  
  <span class="token punctuation">)</span><span class="token keyword">ENGINE</span><span class="token operator">=</span>AggregatingMergeTree<span class="token punctuation">(</span><span class="token punctuation">)</span>
  <span class="token keyword">ORDER</span> <span class="token keyword">BY</span> <span class="token punctuation">(</span>emp_id<span class="token punctuation">,</span>name<span class="token punctuation">)</span> <span class="token comment">-- 注意排序key是两个字段</span>
  <span class="token keyword">PRIMARY</span> <span class="token keyword">KEY</span> emp_id     <span class="token comment">-- 主键是一个字段</span>
  <span class="token keyword">PARTITION</span> <span class="token keyword">BY</span> work_place
  <span class="token punctuation">;</span>
</code></pre> 
<p>对于AggregateFunction类型的列字段，在进行数据的写入和查询时与其他的表引擎有很大区别，在写入数据时，需要调用**-State<strong>函数；而在查询数据时，则需要调用相应的</strong>-Merge<strong>函数。对于上面的建表语句而言，需要使用</strong>sumState**函数进行数据插入</p> 
<pre class=" language-sql"><code class="prism  language-sql"><span class="token comment">-- 插入数据，</span>
<span class="token comment">-- 注意：需要使用INSERT…SELECT语句进行数据插入</span>
<span class="token keyword">INSERT</span> <span class="token keyword">INTO</span> <span class="token keyword">TABLE</span> emp_aggregatingmergeTree
<span class="token keyword">SELECT</span> <span class="token number">1</span><span class="token punctuation">,</span><span class="token string">'tom'</span><span class="token punctuation">,</span><span class="token string">'上海'</span><span class="token punctuation">,</span><span class="token number">25</span><span class="token punctuation">,</span><span class="token string">'信息部'</span><span class="token punctuation">,</span>sumState<span class="token punctuation">(</span>toDecimal32<span class="token punctuation">(</span><span class="token number">10000</span><span class="token punctuation">,</span><span class="token number">2</span><span class="token punctuation">)</span><span class="token punctuation">)</span><span class="token punctuation">;</span>
<span class="token keyword">INSERT</span> <span class="token keyword">INTO</span> <span class="token keyword">TABLE</span> emp_aggregatingmergeTree
<span class="token keyword">SELECT</span> <span class="token number">1</span><span class="token punctuation">,</span><span class="token string">'tom'</span><span class="token punctuation">,</span><span class="token string">'上海'</span><span class="token punctuation">,</span><span class="token number">25</span><span class="token punctuation">,</span><span class="token string">'信息部'</span><span class="token punctuation">,</span>sumState<span class="token punctuation">(</span>toDecimal32<span class="token punctuation">(</span><span class="token number">20000</span><span class="token punctuation">,</span><span class="token number">2</span><span class="token punctuation">)</span><span class="token punctuation">)</span><span class="token punctuation">;</span>
<span class="token comment">-- 查询数据</span>
<span class="token keyword">SELECT</span> 
  emp_id<span class="token punctuation">,</span>
  name <span class="token punctuation">,</span> 
  sumMerge<span class="token punctuation">(</span>salary<span class="token punctuation">)</span> 
<span class="token keyword">FROM</span> emp_aggregatingmergeTree
<span class="token keyword">GROUP</span> <span class="token keyword">BY</span> emp_id<span class="token punctuation">,</span>name<span class="token punctuation">;</span>
<span class="token comment">-- 结果输出</span>
┌─emp_id─┬─name─┬─sumMerge<span class="token punctuation">(</span>salary<span class="token punctuation">)</span>─┐
│      <span class="token number">1</span> │ tom  │         <span class="token number">30000.00</span> │
└────────┴──────┴──────────────────┘
</code></pre> 
<p>上面演示的用法非常的麻烦，其实更多的情况下，我们可以结合物化视图一起使用，将它作为物化视图的表引擎。而这里的物化视图是作为其他数据表上层的一种查询视图。</p> 
<p><strong>AggregatingMergeTree通常作为物化视图的表引擎，与普通MergeTree搭配使用。</strong></p> 
<pre class=" language-sql"><code class="prism  language-sql"><span class="token comment">-- 创建一个MereTree引擎的明细表</span>
<span class="token comment">-- 用于存储全量的明细数据</span>
<span class="token comment">-- 对外提供实时查询</span>
<span class="token keyword">CREATE</span> <span class="token keyword">TABLE</span> emp_mergetree_base <span class="token punctuation">(</span>
  emp_id UInt16 <span class="token keyword">COMMENT</span> <span class="token string">'员工id'</span><span class="token punctuation">,</span>
  name String <span class="token keyword">COMMENT</span> <span class="token string">'员工姓名'</span><span class="token punctuation">,</span>
  work_place String <span class="token keyword">COMMENT</span> <span class="token string">'工作地点'</span><span class="token punctuation">,</span>
  age UInt8 <span class="token keyword">COMMENT</span> <span class="token string">'员工年龄'</span><span class="token punctuation">,</span>
  depart String <span class="token keyword">COMMENT</span> <span class="token string">'部门'</span><span class="token punctuation">,</span>
  salary Decimal32<span class="token punctuation">(</span><span class="token number">2</span><span class="token punctuation">)</span> <span class="token keyword">COMMENT</span> <span class="token string">'工资'</span>
  <span class="token punctuation">)</span><span class="token keyword">ENGINE</span><span class="token operator">=</span>MergeTree<span class="token punctuation">(</span><span class="token punctuation">)</span>
  <span class="token keyword">ORDER</span> <span class="token keyword">BY</span> <span class="token punctuation">(</span>emp_id<span class="token punctuation">,</span>name<span class="token punctuation">)</span>
  <span class="token keyword">PARTITION</span> <span class="token keyword">BY</span> work_place
  <span class="token punctuation">;</span>
  
<span class="token comment">-- 创建一张物化视图</span>
<span class="token comment">-- 使用AggregatingMergeTree表引擎</span>
<span class="token keyword">CREATE</span> MATERIALIZED <span class="token keyword">VIEW</span> view_emp_agg
<span class="token keyword">ENGINE</span> <span class="token operator">=</span> AggregatingMergeTree<span class="token punctuation">(</span><span class="token punctuation">)</span>
<span class="token keyword">PARTITION</span> <span class="token keyword">BY</span> emp_id
<span class="token keyword">ORDER</span> <span class="token keyword">BY</span> <span class="token punctuation">(</span>emp_id<span class="token punctuation">,</span>name<span class="token punctuation">)</span>
<span class="token keyword">AS</span> <span class="token keyword">SELECT</span>
     emp_id<span class="token punctuation">,</span>
     name<span class="token punctuation">,</span>
     sumState<span class="token punctuation">(</span>salary<span class="token punctuation">)</span> <span class="token keyword">AS</span> salary
<span class="token keyword">FROM</span> emp_mergetree_base
<span class="token keyword">GROUP</span> <span class="token keyword">BY</span> emp_id<span class="token punctuation">,</span>name<span class="token punctuation">;</span>

<span class="token comment">-- 向基础明细表emp_mergetree_base插入数据</span>
<span class="token keyword">INSERT</span> <span class="token keyword">INTO</span> emp_mergetree_base
<span class="token keyword">VALUES</span> <span class="token punctuation">(</span><span class="token number">1</span><span class="token punctuation">,</span><span class="token string">'tom'</span><span class="token punctuation">,</span><span class="token string">'上海'</span><span class="token punctuation">,</span><span class="token number">25</span><span class="token punctuation">,</span><span class="token string">'技术部'</span><span class="token punctuation">,</span><span class="token number">20000</span><span class="token punctuation">)</span><span class="token punctuation">,</span>
<span class="token punctuation">(</span><span class="token number">1</span><span class="token punctuation">,</span><span class="token string">'tom'</span><span class="token punctuation">,</span><span class="token string">'上海'</span><span class="token punctuation">,</span><span class="token number">26</span><span class="token punctuation">,</span><span class="token string">'人事部'</span><span class="token punctuation">,</span><span class="token number">10000</span><span class="token punctuation">)</span><span class="token punctuation">;</span>

<span class="token comment">-- 查询物化视图</span>
<span class="token keyword">SELECT</span> 
  emp_id<span class="token punctuation">,</span>
  name <span class="token punctuation">,</span> 
  sumMerge<span class="token punctuation">(</span>salary<span class="token punctuation">)</span> 
<span class="token keyword">FROM</span> view_emp_agg
<span class="token keyword">GROUP</span> <span class="token keyword">BY</span> emp_id<span class="token punctuation">,</span>name<span class="token punctuation">;</span>
<span class="token comment">-- 结果</span>
┌─emp_id─┬─name─┬─sumMerge<span class="token punctuation">(</span>salary<span class="token punctuation">)</span>─┐
│      <span class="token number">1</span> │ tom  │         <span class="token number">30000.00</span> │
└────────┴──────┴──────────────────┘
</code></pre> 
<h3><a id="CollapsingMergeTree_875"></a>CollapsingMergeTree表引擎</h3> 
<p>CollapsingMergeTree就是一种通过以增代删的思路，支持行级数据修改和删除的表引擎。它通过定义一个sign标记位字段，记录数据行的状态。如果sign标记为1，则表示这是一行有效的数据；如果sign标记为-1，则表示这行数据需要被删除。当CollapsingMergeTree分区合并时，同一数据分区内，sign标记为1和-1的一组数据会被抵消删除。</p> 
<p>每次需要新增数据时，写入一行sign标记为1的数据；需要删除数据时，则写入一行sign标记为-1的数据。</p> 
<h4><a id="_881"></a>建表语法</h4> 
<pre class=" language-sql"><code class="prism  language-sql"><span class="token keyword">CREATE</span> <span class="token keyword">TABLE</span> <span class="token punctuation">[</span><span class="token keyword">IF</span> <span class="token operator">NOT</span> <span class="token keyword">EXISTS</span><span class="token punctuation">]</span> <span class="token punctuation">[</span>db<span class="token punctuation">.</span><span class="token punctuation">]</span>table_name <span class="token punctuation">[</span><span class="token keyword">ON</span> CLUSTER cluster<span class="token punctuation">]</span>
<span class="token punctuation">(</span>
    name1 <span class="token punctuation">[</span>type1<span class="token punctuation">]</span> <span class="token punctuation">[</span><span class="token keyword">DEFAULT</span><span class="token operator">|</span>MATERIALIZED<span class="token operator">|</span>ALIAS expr1<span class="token punctuation">]</span><span class="token punctuation">,</span>
    name2 <span class="token punctuation">[</span>type2<span class="token punctuation">]</span> <span class="token punctuation">[</span><span class="token keyword">DEFAULT</span><span class="token operator">|</span>MATERIALIZED<span class="token operator">|</span>ALIAS expr2<span class="token punctuation">]</span><span class="token punctuation">,</span>
    <span class="token punctuation">.</span><span class="token punctuation">.</span><span class="token punctuation">.</span>
<span class="token punctuation">)</span> <span class="token keyword">ENGINE</span> <span class="token operator">=</span> CollapsingMergeTree<span class="token punctuation">(</span>sign<span class="token punctuation">)</span>
<span class="token punctuation">[</span><span class="token keyword">PARTITION</span> <span class="token keyword">BY</span> expr<span class="token punctuation">]</span>
<span class="token punctuation">[</span><span class="token keyword">ORDER</span> <span class="token keyword">BY</span> expr<span class="token punctuation">]</span>
<span class="token punctuation">[</span>SAMPLE <span class="token keyword">BY</span> expr<span class="token punctuation">]</span>
<span class="token punctuation">[</span>SETTINGS name<span class="token operator">=</span><span class="token keyword">value</span><span class="token punctuation">,</span> <span class="token punctuation">.</span><span class="token punctuation">.</span><span class="token punctuation">.</span><span class="token punctuation">]</span>
</code></pre> 
<h4><a id="_896"></a>建表示例</h4> 
<p>上面的建表语句使用CollapsingMergeTree(sign)，其中字段sign是一个Int8类型的字段</p> 
<pre class=" language-sql"><code class="prism  language-sql"><span class="token keyword">CREATE</span> <span class="token keyword">TABLE</span> emp_collapsingmergetree <span class="token punctuation">(</span>
  emp_id UInt16 <span class="token keyword">COMMENT</span> <span class="token string">'员工id'</span><span class="token punctuation">,</span>
  name String <span class="token keyword">COMMENT</span> <span class="token string">'员工姓名'</span><span class="token punctuation">,</span>
  work_place String <span class="token keyword">COMMENT</span> <span class="token string">'工作地点'</span><span class="token punctuation">,</span>
  age UInt8 <span class="token keyword">COMMENT</span> <span class="token string">'员工年龄'</span><span class="token punctuation">,</span>
  depart String <span class="token keyword">COMMENT</span> <span class="token string">'部门'</span><span class="token punctuation">,</span>
  salary Decimal32<span class="token punctuation">(</span><span class="token number">2</span><span class="token punctuation">)</span> <span class="token keyword">COMMENT</span> <span class="token string">'工资'</span><span class="token punctuation">,</span>
  sign Int8
  <span class="token punctuation">)</span><span class="token keyword">ENGINE</span><span class="token operator">=</span>CollapsingMergeTree<span class="token punctuation">(</span>sign<span class="token punctuation">)</span>
  <span class="token keyword">ORDER</span> <span class="token keyword">BY</span> <span class="token punctuation">(</span>emp_id<span class="token punctuation">,</span>name<span class="token punctuation">)</span>
  <span class="token keyword">PARTITION</span> <span class="token keyword">BY</span> work_place
  <span class="token punctuation">;</span> 
  
</code></pre> 
<h4><a id="_916"></a>使用方式</h4> 
<p>CollapsingMergeTree同样是以ORDER BY排序键作为判断数据唯一性的依据。</p> 
<pre class=" language-sql"><code class="prism  language-sql"><span class="token comment">-- 插入新增数据,sign=1表示正常数据</span>
<span class="token keyword">INSERT</span> <span class="token keyword">INTO</span> emp_collapsingmergetree 
<span class="token keyword">VALUES</span> <span class="token punctuation">(</span><span class="token number">1</span><span class="token punctuation">,</span><span class="token string">'tom'</span><span class="token punctuation">,</span><span class="token string">'上海'</span><span class="token punctuation">,</span><span class="token number">25</span><span class="token punctuation">,</span><span class="token string">'技术部'</span><span class="token punctuation">,</span><span class="token number">20000</span><span class="token punctuation">,</span><span class="token number">1</span><span class="token punctuation">)</span><span class="token punctuation">;</span>

<span class="token comment">-- 更新上述的数据</span>
<span class="token comment">-- 首先插入一条与原来相同的数据(ORDER BY字段一致),并将sign置为-1</span>
<span class="token keyword">INSERT</span> <span class="token keyword">INTO</span> emp_collapsingmergetree 
<span class="token keyword">VALUES</span> <span class="token punctuation">(</span><span class="token number">1</span><span class="token punctuation">,</span><span class="token string">'tom'</span><span class="token punctuation">,</span><span class="token string">'上海'</span><span class="token punctuation">,</span><span class="token number">25</span><span class="token punctuation">,</span><span class="token string">'技术部'</span><span class="token punctuation">,</span><span class="token number">20000</span><span class="token punctuation">,</span><span class="token operator">-</span><span class="token number">1</span><span class="token punctuation">)</span><span class="token punctuation">;</span>

<span class="token comment">-- 再插入更新之后的数据</span>
<span class="token keyword">INSERT</span> <span class="token keyword">INTO</span> emp_collapsingmergetree 
<span class="token keyword">VALUES</span> <span class="token punctuation">(</span><span class="token number">1</span><span class="token punctuation">,</span><span class="token string">'tom'</span><span class="token punctuation">,</span><span class="token string">'上海'</span><span class="token punctuation">,</span><span class="token number">25</span><span class="token punctuation">,</span><span class="token string">'技术部'</span><span class="token punctuation">,</span><span class="token number">30000</span><span class="token punctuation">,</span><span class="token number">1</span><span class="token punctuation">)</span><span class="token punctuation">;</span>

<span class="token comment">-- 查看一下结果</span>
cdh04 :<span class="token punctuation">)</span> <span class="token keyword">select</span> <span class="token operator">*</span> <span class="token keyword">from</span> emp_collapsingmergetree <span class="token punctuation">;</span>

<span class="token keyword">SELECT</span> <span class="token operator">*</span>
<span class="token keyword">FROM</span> emp_collapsingmergetree

┌─emp_id─┬─name─┬─work_place─┬─age─┬─depart─┬───salary─┬─sign─┐
│      <span class="token number">1</span> │ tom  │ 上海       │  <span class="token number">25</span> │ 技术部 │ <span class="token number">30000.00</span> │    <span class="token number">1</span> │
└────────┴──────┴────────────┴─────┴────────┴──────────┴──────┘
┌─emp_id─┬─name─┬─work_place─┬─age─┬─depart─┬───salary─┬─sign─┐
│      <span class="token number">1</span> │ tom  │ 上海       │  <span class="token number">25</span> │ 技术部 │ <span class="token number">20000.00</span> │   <span class="token operator">-</span><span class="token number">1</span> │
└────────┴──────┴────────────┴─────┴────────┴──────────┴──────┘
┌─emp_id─┬─name─┬─work_place─┬─age─┬─depart─┬───salary─┬─sign─┐
│      <span class="token number">1</span> │ tom  │ 上海       │  <span class="token number">25</span> │ 技术部 │ <span class="token number">20000.00</span> │    <span class="token number">1</span> │
└────────┴──────┴────────────┴─────┴────────┴──────────┴──────┘
<span class="token comment">-- 执行分区合并操作</span>
<span class="token keyword">optimize</span> <span class="token keyword">table</span> emp_collapsingmergetree<span class="token punctuation">;</span>
<span class="token comment">-- 再次查询，sign=1与sign=-1的数据相互抵消了，即被删除</span>
cdh04 :<span class="token punctuation">)</span> <span class="token keyword">select</span> <span class="token operator">*</span> <span class="token keyword">from</span> emp_collapsingmergetree <span class="token punctuation">;</span>

<span class="token keyword">SELECT</span> <span class="token operator">*</span>
<span class="token keyword">FROM</span> emp_collapsingmergetree

┌─emp_id─┬─name─┬─work_place─┬─age─┬─depart─┬───salary─┬─sign─┐
│      <span class="token number">1</span> │ tom  │ 上海       │  <span class="token number">25</span> │ 技术部 │ <span class="token number">30000.00</span> │    <span class="token number">1</span> │
└────────┴──────┴────────────┴─────┴────────┴──────────┴──────┘
</code></pre> 
<h4><a id="_962"></a>注意点</h4> 
<ul><li><strong>分区合并</strong></li></ul> 
<p>分数数据折叠不是实时的，需要后台进行Compaction操作，用户也可以使用手动合并命令，但是效率会很低，一般不推荐在生产环境中使用。</p> 
<p>当进行汇总数据操作时，可以通过改变查询方式，来过滤掉被删除的数据</p> 
<pre class=" language-sql"><code class="prism  language-sql"><span class="token keyword">SELECT</span> 
    emp_id<span class="token punctuation">,</span> 
    name<span class="token punctuation">,</span> 
    <span class="token function">sum</span><span class="token punctuation">(</span>salary <span class="token operator">*</span> sign<span class="token punctuation">)</span>
<span class="token keyword">FROM</span> emp_collapsingmergetree
<span class="token keyword">GROUP</span> <span class="token keyword">BY</span> 
    emp_id<span class="token punctuation">,</span> 
    name
<span class="token keyword">HAVING</span> <span class="token function">sum</span><span class="token punctuation">(</span>sign<span class="token punctuation">)</span> <span class="token operator">&gt;</span> <span class="token number">0</span>
</code></pre> 
<p>只有相同分区内的数据才有可能被折叠。其实，当我们修改或删除数据时，这些被修改的数据通常是在一个分区内的，所以不会产生影响。</p> 
<ul><li>数据写入顺序</li></ul> 
<p>值得注意的是：CollapsingMergeTree对于写入数据的顺序有着严格要求，否则导致无法正常折叠。</p> 
<pre class=" language-sql"><code class="prism  language-sql"><span class="token comment">-- 建表</span>
<span class="token keyword">CREATE</span> <span class="token keyword">TABLE</span> emp_collapsingmergetree_order <span class="token punctuation">(</span>
  emp_id UInt16 <span class="token keyword">COMMENT</span> <span class="token string">'员工id'</span><span class="token punctuation">,</span>
  name String <span class="token keyword">COMMENT</span> <span class="token string">'员工姓名'</span><span class="token punctuation">,</span>
  work_place String <span class="token keyword">COMMENT</span> <span class="token string">'工作地点'</span><span class="token punctuation">,</span>
  age UInt8 <span class="token keyword">COMMENT</span> <span class="token string">'员工年龄'</span><span class="token punctuation">,</span>
  depart String <span class="token keyword">COMMENT</span> <span class="token string">'部门'</span><span class="token punctuation">,</span>
  salary Decimal32<span class="token punctuation">(</span><span class="token number">2</span><span class="token punctuation">)</span> <span class="token keyword">COMMENT</span> <span class="token string">'工资'</span><span class="token punctuation">,</span>
  sign Int8
  <span class="token punctuation">)</span><span class="token keyword">ENGINE</span><span class="token operator">=</span>CollapsingMergeTree<span class="token punctuation">(</span>sign<span class="token punctuation">)</span>
  <span class="token keyword">ORDER</span> <span class="token keyword">BY</span> <span class="token punctuation">(</span>emp_id<span class="token punctuation">,</span>name<span class="token punctuation">)</span>
  <span class="token keyword">PARTITION</span> <span class="token keyword">BY</span> work_place
  <span class="token punctuation">;</span> 
  
<span class="token comment">-- 先插入需要被删除的数据，即sign=-1的数据</span>
<span class="token keyword">INSERT</span> <span class="token keyword">INTO</span> emp_collapsingmergetree_order 
<span class="token keyword">VALUES</span> <span class="token punctuation">(</span><span class="token number">1</span><span class="token punctuation">,</span><span class="token string">'tom'</span><span class="token punctuation">,</span><span class="token string">'上海'</span><span class="token punctuation">,</span><span class="token number">25</span><span class="token punctuation">,</span><span class="token string">'技术部'</span><span class="token punctuation">,</span><span class="token number">20000</span><span class="token punctuation">,</span><span class="token operator">-</span><span class="token number">1</span><span class="token punctuation">)</span><span class="token punctuation">;</span>
<span class="token comment">-- 再插入sign=1的数据</span>
<span class="token keyword">INSERT</span> <span class="token keyword">INTO</span> emp_collapsingmergetree_order 
<span class="token keyword">VALUES</span> <span class="token punctuation">(</span><span class="token number">1</span><span class="token punctuation">,</span><span class="token string">'tom'</span><span class="token punctuation">,</span><span class="token string">'上海'</span><span class="token punctuation">,</span><span class="token number">25</span><span class="token punctuation">,</span><span class="token string">'技术部'</span><span class="token punctuation">,</span><span class="token number">20000</span><span class="token punctuation">,</span><span class="token number">1</span><span class="token punctuation">)</span><span class="token punctuation">;</span>
<span class="token comment">-- 查询表</span>
<span class="token keyword">SELECT</span> <span class="token operator">*</span>
<span class="token keyword">FROM</span> emp_collapsingmergetree_order

┌─emp_id─┬─name─┬─work_place─┬─age─┬─depart─┬───salary─┬─sign─┐
│      <span class="token number">1</span> │ tom  │ 上海       │  <span class="token number">25</span> │ 技术部 │ <span class="token number">20000.00</span> │    <span class="token number">1</span> │
└────────┴──────┴────────────┴─────┴────────┴──────────┴──────┘
┌─emp_id─┬─name─┬─work_place─┬─age─┬─depart─┬───salary─┬─sign─┐
│      <span class="token number">1</span> │ tom  │ 上海       │  <span class="token number">25</span> │ 技术部 │ <span class="token number">20000.00</span> │   <span class="token operator">-</span><span class="token number">1</span> │
└────────┴──────┴────────────┴─────┴────────┴──────────┴──────┘
<span class="token comment">-- 执行合并操作</span>
<span class="token keyword">optimize</span> <span class="token keyword">table</span> emp_collapsingmergetree_order<span class="token punctuation">;</span>
<span class="token comment">-- 再次查询表</span>
<span class="token comment">-- 旧数据依然存在</span>
<span class="token keyword">SELECT</span> <span class="token operator">*</span>
<span class="token keyword">FROM</span> emp_collapsingmergetree_order<span class="token punctuation">;</span>
┌─emp_id─┬─name─┬─work_place─┬─age─┬─depart─┬───salary─┬─sign─┐
│      <span class="token number">1</span> │ tom  │ 上海       │  <span class="token number">25</span> │ 技术部 │ <span class="token number">20000.00</span> │   <span class="token operator">-</span><span class="token number">1</span> │
│      <span class="token number">1</span> │ tom  │ 上海       │  <span class="token number">25</span> │ 技术部 │ <span class="token number">20000.00</span> │    <span class="token number">1</span> │
└────────┴──────┴────────────┴─────┴────────┴──────────┴──────┘
</code></pre> 
<p>如果数据的写入程序是单线程执行的，则能够较好地控制写入顺序；如果需要处理的数据量很大，数据的写入程序通常是多线程执行的，那么此时就不能保障数据的写入顺序了。在这种情况下，CollapsingMergeTree的工作机制就会出现问题。但是可以通过VersionedCollapsingMergeTree的表引擎得到解决。</p> 
<h3><a id="VersionedCollapsingMergeTree_1033"></a>VersionedCollapsingMergeTree表引擎</h3> 
<p>上面提到CollapsingMergeTree表引擎对于数据写入乱序的情况下，不能够实现数据折叠的效果。VersionedCollapsingMergeTree表引擎的作用与CollapsingMergeTree完全相同，它们的不同之处在于，VersionedCollapsingMergeTree对数据的写入顺序没有要求，在同一个分区内，任意顺序的数据都能够完成折叠操作。</p> 
<p>VersionedCollapsingMergeTree使用<strong>version</strong>列来实现乱序情况下的数据折叠。</p> 
<h4><a id="_1039"></a>建表语法</h4> 
<pre class=" language-sql"><code class="prism  language-sql"><span class="token keyword">CREATE</span> <span class="token keyword">TABLE</span> <span class="token punctuation">[</span><span class="token keyword">IF</span> <span class="token operator">NOT</span> <span class="token keyword">EXISTS</span><span class="token punctuation">]</span> <span class="token punctuation">[</span>db<span class="token punctuation">.</span><span class="token punctuation">]</span>table_name <span class="token punctuation">[</span><span class="token keyword">ON</span> CLUSTER cluster<span class="token punctuation">]</span>
<span class="token punctuation">(</span>
    name1 <span class="token punctuation">[</span>type1<span class="token punctuation">]</span> <span class="token punctuation">[</span><span class="token keyword">DEFAULT</span><span class="token operator">|</span>MATERIALIZED<span class="token operator">|</span>ALIAS expr1<span class="token punctuation">]</span><span class="token punctuation">,</span>
    name2 <span class="token punctuation">[</span>type2<span class="token punctuation">]</span> <span class="token punctuation">[</span><span class="token keyword">DEFAULT</span><span class="token operator">|</span>MATERIALIZED<span class="token operator">|</span>ALIAS expr2<span class="token punctuation">]</span><span class="token punctuation">,</span>
    <span class="token punctuation">.</span><span class="token punctuation">.</span><span class="token punctuation">.</span>
<span class="token punctuation">)</span> <span class="token keyword">ENGINE</span> <span class="token operator">=</span> VersionedCollapsingMergeTree<span class="token punctuation">(</span>sign<span class="token punctuation">,</span> version<span class="token punctuation">)</span>
<span class="token punctuation">[</span><span class="token keyword">PARTITION</span> <span class="token keyword">BY</span> expr<span class="token punctuation">]</span>
<span class="token punctuation">[</span><span class="token keyword">ORDER</span> <span class="token keyword">BY</span> expr<span class="token punctuation">]</span>
<span class="token punctuation">[</span>SAMPLE <span class="token keyword">BY</span> expr<span class="token punctuation">]</span>
<span class="token punctuation">[</span>SETTINGS name<span class="token operator">=</span><span class="token keyword">value</span><span class="token punctuation">,</span> <span class="token punctuation">.</span><span class="token punctuation">.</span><span class="token punctuation">.</span><span class="token punctuation">]</span>
</code></pre> 
<p>可以看出：该引擎除了需要指定一个sign标识之外，还需要指定一个UInt8类型的version版本号。</p> 
<h4><a id="_1056"></a>建表示例</h4> 
<pre class=" language-sql"><code class="prism  language-sql"><span class="token keyword">CREATE</span> <span class="token keyword">TABLE</span> emp_versioned <span class="token punctuation">(</span>
  emp_id UInt16 <span class="token keyword">COMMENT</span> <span class="token string">'员工id'</span><span class="token punctuation">,</span>
  name String <span class="token keyword">COMMENT</span> <span class="token string">'员工姓名'</span><span class="token punctuation">,</span>
  work_place String <span class="token keyword">COMMENT</span> <span class="token string">'工作地点'</span><span class="token punctuation">,</span>
  age UInt8 <span class="token keyword">COMMENT</span> <span class="token string">'员工年龄'</span><span class="token punctuation">,</span>
  depart String <span class="token keyword">COMMENT</span> <span class="token string">'部门'</span><span class="token punctuation">,</span>
  salary Decimal32<span class="token punctuation">(</span><span class="token number">2</span><span class="token punctuation">)</span> <span class="token keyword">COMMENT</span> <span class="token string">'工资'</span><span class="token punctuation">,</span>
  sign Int8<span class="token punctuation">,</span>
  version Int8
  <span class="token punctuation">)</span><span class="token keyword">ENGINE</span><span class="token operator">=</span>VersionedCollapsingMergeTree<span class="token punctuation">(</span>sign<span class="token punctuation">,</span> version<span class="token punctuation">)</span>
  <span class="token keyword">ORDER</span> <span class="token keyword">BY</span> <span class="token punctuation">(</span>emp_id<span class="token punctuation">,</span>name<span class="token punctuation">)</span>
  <span class="token keyword">PARTITION</span> <span class="token keyword">BY</span> work_place
  <span class="token punctuation">;</span>
  
  <span class="token comment">-- 先插入需要被删除的数据，即sign=-1的数据</span>
<span class="token keyword">INSERT</span> <span class="token keyword">INTO</span> emp_versioned 
<span class="token keyword">VALUES</span> <span class="token punctuation">(</span><span class="token number">1</span><span class="token punctuation">,</span><span class="token string">'tom'</span><span class="token punctuation">,</span><span class="token string">'上海'</span><span class="token punctuation">,</span><span class="token number">25</span><span class="token punctuation">,</span><span class="token string">'技术部'</span><span class="token punctuation">,</span><span class="token number">20000</span><span class="token punctuation">,</span><span class="token operator">-</span><span class="token number">1</span><span class="token punctuation">,</span><span class="token number">1</span><span class="token punctuation">)</span><span class="token punctuation">;</span>
<span class="token comment">-- 再插入sign=1的数据</span>
<span class="token keyword">INSERT</span> <span class="token keyword">INTO</span> emp_versioned 
<span class="token keyword">VALUES</span> <span class="token punctuation">(</span><span class="token number">1</span><span class="token punctuation">,</span><span class="token string">'tom'</span><span class="token punctuation">,</span><span class="token string">'上海'</span><span class="token punctuation">,</span><span class="token number">25</span><span class="token punctuation">,</span><span class="token string">'技术部'</span><span class="token punctuation">,</span><span class="token number">20000</span><span class="token punctuation">,</span><span class="token number">1</span><span class="token punctuation">,</span><span class="token number">1</span><span class="token punctuation">)</span><span class="token punctuation">;</span>
<span class="token comment">-- 在插入一个新版本数据</span>
<span class="token keyword">INSERT</span> <span class="token keyword">INTO</span> emp_versioned 
<span class="token keyword">VALUES</span> <span class="token punctuation">(</span><span class="token number">1</span><span class="token punctuation">,</span><span class="token string">'tom'</span><span class="token punctuation">,</span><span class="token string">'上海'</span><span class="token punctuation">,</span><span class="token number">25</span><span class="token punctuation">,</span><span class="token string">'技术部'</span><span class="token punctuation">,</span><span class="token number">30000</span><span class="token punctuation">,</span><span class="token number">1</span><span class="token punctuation">,</span><span class="token number">2</span><span class="token punctuation">)</span><span class="token punctuation">;</span>

<span class="token comment">-- 先不执行合并，查看表数据</span>
cdh04 :<span class="token punctuation">)</span> <span class="token keyword">select</span> <span class="token operator">*</span> <span class="token keyword">from</span> emp_versioned<span class="token punctuation">;</span>

<span class="token keyword">SELECT</span> <span class="token operator">*</span>
<span class="token keyword">FROM</span> emp_versioned

┌─emp_id─┬─name─┬─work_place─┬─age─┬─depart─┬───salary─┬─sign─┬─version─┐
│      <span class="token number">1</span> │ tom  │ 上海       │  <span class="token number">25</span> │ 技术部 │ <span class="token number">30000.00</span> │    <span class="token number">1</span> │       <span class="token number">2</span> │
└────────┴──────┴────────────┴─────┴────────┴──────────┴──────┴─────────┘
┌─emp_id─┬─name─┬─work_place─┬─age─┬─depart─┬───salary─┬─sign─┬─version─┐
│      <span class="token number">1</span> │ tom  │ 上海       │  <span class="token number">25</span> │ 技术部 │ <span class="token number">20000.00</span> │    <span class="token number">1</span> │       <span class="token number">1</span> │
└────────┴──────┴────────────┴─────┴────────┴──────────┴──────┴─────────┘
┌─emp_id─┬─name─┬─work_place─┬─age─┬─depart─┬───salary─┬─sign─┬─version─┐
│      <span class="token number">1</span> │ tom  │ 上海       │  <span class="token number">25</span> │ 技术部 │ <span class="token number">20000.00</span> │   <span class="token operator">-</span><span class="token number">1</span> │       <span class="token number">1</span> │
└────────┴──────┴────────────┴─────┴────────┴──────────┴──────┴─────────┘

<span class="token comment">-- 获取正确查询结果</span>
<span class="token keyword">SELECT</span> 
    emp_id<span class="token punctuation">,</span> 
    name<span class="token punctuation">,</span> 
    <span class="token function">sum</span><span class="token punctuation">(</span>salary <span class="token operator">*</span> sign<span class="token punctuation">)</span>
<span class="token keyword">FROM</span> emp_versioned
<span class="token keyword">GROUP</span> <span class="token keyword">BY</span> 
    emp_id<span class="token punctuation">,</span> 
    name
<span class="token keyword">HAVING</span> <span class="token function">sum</span><span class="token punctuation">(</span>sign<span class="token punctuation">)</span> <span class="token operator">&gt;</span> <span class="token number">0</span><span class="token punctuation">;</span>

<span class="token comment">-- 手动合并</span>
<span class="token keyword">optimize</span> <span class="token keyword">table</span> emp_versioned<span class="token punctuation">;</span>

<span class="token comment">-- 再次查询</span>
cdh04 :<span class="token punctuation">)</span> <span class="token keyword">select</span> <span class="token operator">*</span> <span class="token keyword">from</span> emp_versioned<span class="token punctuation">;</span>

<span class="token keyword">SELECT</span> <span class="token operator">*</span>
<span class="token keyword">FROM</span> emp_versioned

┌─emp_id─┬─name─┬─work_place─┬─age─┬─depart─┬───salary─┬─sign─┬─version─┐
│      <span class="token number">1</span> │ tom  │ 上海       │  <span class="token number">25</span> │ 技术部 │ <span class="token number">30000.00</span> │    <span class="token number">1</span> │       <span class="token number">2</span> │
└────────┴──────┴────────────┴─────┴────────┴──────────┴──────┴─────────┘
</code></pre> 
<p>可见上面虽然在插入数据乱序的情况下，依然能够实现折叠的效果。之所以能够达到这种效果，是因为在定义version字段之后，VersionedCollapsingMergeTree会自动将version作为排序条件并增加到ORDER BY的末端，就上述的例子而言，最终的排序字段为ORDER BY emp_id,name，version desc。</p> 
<h3><a id="GraphiteMergeTree_1126"></a>GraphiteMergeTree表引擎</h3> 
<p>该引擎用来对 Graphite数据进行’瘦身’及汇总。对于想使用CH来存储Graphite数据的开发者来说可能有用。</p> 
<p>如果不需要对Graphite数据做汇总，那么可以使用任意的CH表引擎；但若需要，那就采用 GraphiteMergeTree 引擎。它能减少存储空间，同时能提高Graphite数据的查询效率。</p> 
<h2><a id="_1132"></a>外部集成表引擎</h2> 
<p>ClickHouse提供了许多与外部系统集成的方法，包括一些表引擎。这些表引擎与其他类型的表引擎类似，可以用于将外部数据导入到ClickHouse中，或者在ClickHouse中直接操作外部数据源。</p> 
<p>例如直接读取HDFS的文件或者MySQL数据库的表。这些表引擎只负责元数据管理和数据查询，而它们自身通常并不负责数据的写入，数据文件直接由外部系统提供。目前ClickHouse提供了下面的外部集成表引擎：</p> 
<ul><li><strong>ODBC</strong>：通过指定odbc连接读取数据源</li><li><strong>JDBC</strong>：通过指定jdbc连接读取数据源；</li><li><strong>MySQL</strong>：将MySQL作为数据存储，直接查询其数据</li><li><strong>HDFS</strong>：直接读取HDFS上的特定格式的数据文件；</li><li><strong>Kafka</strong>：将Kafka数据导入ClickHouse</li><li><strong>RabbitMQ</strong>：与Kafka类似</li></ul> 
<h3><a id="HDFS_1145"></a>HDFS</h3> 
<h4><a id="_1147"></a>使用方式</h4> 
<pre class=" language-sql"><code class="prism  language-sql"><span class="token keyword">ENGINE</span> <span class="token operator">=</span> HDFS<span class="token punctuation">(</span>URI<span class="token punctuation">,</span> format<span class="token punctuation">)</span>
</code></pre> 
<ul><li>URI：HDFS文件路径</li><li>format：文件格式，比如CSV、JSON、TSV等</li></ul> 
<h4><a id="_1156"></a>使用示例</h4> 
<pre class=" language-sql"><code class="prism  language-sql"><span class="token comment">-- 建表</span>
<span class="token keyword">CREATE</span> <span class="token keyword">TABLE</span> hdfs_engine_table<span class="token punctuation">(</span>
  emp_id UInt16 <span class="token keyword">COMMENT</span> <span class="token string">'员工id'</span><span class="token punctuation">,</span>
  name String <span class="token keyword">COMMENT</span> <span class="token string">'员工姓名'</span><span class="token punctuation">,</span>
  work_place String <span class="token keyword">COMMENT</span> <span class="token string">'工作地点'</span><span class="token punctuation">,</span>
  age UInt8 <span class="token keyword">COMMENT</span> <span class="token string">'员工年龄'</span><span class="token punctuation">,</span>
  depart String <span class="token keyword">COMMENT</span> <span class="token string">'部门'</span><span class="token punctuation">,</span>
  salary Decimal32<span class="token punctuation">(</span><span class="token number">2</span><span class="token punctuation">)</span> <span class="token keyword">COMMENT</span> <span class="token string">'工资'</span>
<span class="token punctuation">)</span> <span class="token keyword">ENGINE</span><span class="token operator">=</span>HDFS<span class="token punctuation">(</span><span class="token string">'hdfs://cdh03:8020/user/hive/hdfs_engine_table'</span><span class="token punctuation">,</span> <span class="token string">'CSV'</span><span class="token punctuation">)</span><span class="token punctuation">;</span>

<span class="token comment">-- 写入数据</span>
<span class="token keyword">INSERT</span> <span class="token keyword">INTO</span> hdfs_engine_table 
<span class="token keyword">VALUES</span> <span class="token punctuation">(</span><span class="token number">1</span><span class="token punctuation">,</span><span class="token string">'tom'</span><span class="token punctuation">,</span><span class="token string">'上海'</span><span class="token punctuation">,</span><span class="token number">25</span><span class="token punctuation">,</span><span class="token string">'技术部'</span><span class="token punctuation">,</span><span class="token number">20000</span><span class="token punctuation">)</span><span class="token punctuation">,</span><span class="token punctuation">(</span><span class="token number">2</span><span class="token punctuation">,</span><span class="token string">'jack'</span><span class="token punctuation">,</span><span class="token string">'上海'</span><span class="token punctuation">,</span><span class="token number">26</span><span class="token punctuation">,</span><span class="token string">'人事部'</span><span class="token punctuation">,</span><span class="token number">10000</span><span class="token punctuation">)</span><span class="token punctuation">;</span>
<span class="token comment">-- 查询数据</span>
cdh04 :<span class="token punctuation">)</span> <span class="token keyword">select</span> <span class="token operator">*</span> <span class="token keyword">from</span> hdfs_engine_table<span class="token punctuation">;</span>

<span class="token keyword">SELECT</span> <span class="token operator">*</span>
<span class="token keyword">FROM</span> hdfs_engine_table

┌─emp_id─┬─name─┬─work_place─┬─age─┬─depart─┬───salary─┐
│      <span class="token number">1</span> │ tom  │ 上海       │  <span class="token number">25</span> │ 技术部 │ <span class="token number">20000.00</span> │
│      <span class="token number">2</span> │ jack │ 上海       │  <span class="token number">26</span> │ 人事部 │ <span class="token number">10000.00</span> │
└────────┴──────┴────────────┴─────┴────────┴──────────┘
<span class="token comment">--再在HDFS上其对应的文件，添加几条数据，再次查看</span>
cdh04 :<span class="token punctuation">)</span> <span class="token keyword">select</span> <span class="token operator">*</span> <span class="token keyword">from</span> hdfs_engine_table<span class="token punctuation">;</span>

<span class="token keyword">SELECT</span> <span class="token operator">*</span>
<span class="token keyword">FROM</span> hdfs_engine_table

┌─emp_id─┬─name───┬─work_place─┬─age─┬─depart─┬───salary─┐
│      <span class="token number">1</span> │ tom    │ 上海       │  <span class="token number">25</span> │ 技术部 │ <span class="token number">20000.00</span> │
│      <span class="token number">2</span> │ jack   │ 上海       │  <span class="token number">26</span> │ 人事部 │ <span class="token number">10000.00</span> │
│      <span class="token number">3</span> │ lili   │ 北京       │  <span class="token number">28</span> │ 技术部 │ <span class="token number">20000.00</span> │
│      <span class="token number">4</span> │ jasper │ 杭州       │  <span class="token number">27</span> │ 人事部 │  <span class="token number">8000.00</span> │
└────────┴────────┴────────────┴─────┴────────┴──────────┘
</code></pre> 
<p>可以看出，这种方式与使用Hive类似，我们直接可以将HDFS对应的文件映射成ClickHouse中的一张表，这样就可以使用SQL操作HDFS上的文件了。</p> 
<p>值得注意的是：ClickHouse并不能够删除HDFS上的数据，当我们在ClickHouse客户端中删除了对应的表，只是删除了表结构，HDFS上的文件并没有被删除，这一点跟Hive的外部表十分相似。</p> 
<h3><a id="MySQL_1200"></a>MySQL</h3> 
<p>在上一篇文章[篇一|ClickHouse快速入门]中介绍了MySQL数据库引擎，即ClickHouse可以创建一个MySQL数据引擎，这样就可以在ClickHouse中操作其对应的数据库中的数据。其实，ClickHouse同样支持MySQL表引擎，即映射一张MySQL中的表到ClickHouse中。</p> 
<h4><a id="_1204"></a>使用方式</h4> 
<pre class=" language-sql"><code class="prism  language-sql"><span class="token keyword">CREATE</span> <span class="token keyword">TABLE</span> <span class="token punctuation">[</span><span class="token keyword">IF</span> <span class="token operator">NOT</span> <span class="token keyword">EXISTS</span><span class="token punctuation">]</span> <span class="token punctuation">[</span>db<span class="token punctuation">.</span><span class="token punctuation">]</span>table_name <span class="token punctuation">[</span><span class="token keyword">ON</span> CLUSTER cluster<span class="token punctuation">]</span>
<span class="token punctuation">(</span>
    name1 <span class="token punctuation">[</span>type1<span class="token punctuation">]</span> <span class="token punctuation">[</span><span class="token keyword">DEFAULT</span><span class="token operator">|</span>MATERIALIZED<span class="token operator">|</span>ALIAS expr1<span class="token punctuation">]</span> <span class="token punctuation">[</span>TTL expr1<span class="token punctuation">]</span><span class="token punctuation">,</span>
    name2 <span class="token punctuation">[</span>type2<span class="token punctuation">]</span> <span class="token punctuation">[</span><span class="token keyword">DEFAULT</span><span class="token operator">|</span>MATERIALIZED<span class="token operator">|</span>ALIAS expr2<span class="token punctuation">]</span> <span class="token punctuation">[</span>TTL expr2<span class="token punctuation">]</span><span class="token punctuation">,</span>
    <span class="token punctuation">.</span><span class="token punctuation">.</span><span class="token punctuation">.</span>
<span class="token punctuation">)</span> <span class="token keyword">ENGINE</span> <span class="token operator">=</span> MySQL<span class="token punctuation">(</span><span class="token string">'host:port'</span><span class="token punctuation">,</span> <span class="token string">'database'</span><span class="token punctuation">,</span> <span class="token string">'table'</span><span class="token punctuation">,</span> <span class="token string">'user'</span><span class="token punctuation">,</span> <span class="token string">'password'</span><span class="token punctuation">[</span><span class="token punctuation">,</span> replace_query<span class="token punctuation">,</span> <span class="token string">'on_duplicate_clause'</span><span class="token punctuation">]</span><span class="token punctuation">)</span><span class="token punctuation">;</span>

</code></pre> 
<h4><a id="_1216"></a>使用示例</h4> 
<pre class=" language-sql"><code class="prism  language-sql"><span class="token comment">-- 连接MySQL中clickhouse数据库的test表</span>
<span class="token keyword">CREATE</span> <span class="token keyword">TABLE</span> mysql_engine_table<span class="token punctuation">(</span>
    id Int32<span class="token punctuation">,</span>
    name String
<span class="token punctuation">)</span> <span class="token keyword">ENGINE</span> <span class="token operator">=</span> MySQL<span class="token punctuation">(</span>
 <span class="token string">'192.168.200.241:3306'</span><span class="token punctuation">,</span>
 <span class="token string">'clickhouse'</span><span class="token punctuation">,</span>
 <span class="token string">'test'</span><span class="token punctuation">,</span> 
 <span class="token string">'root'</span><span class="token punctuation">,</span> 
 <span class="token string">'123qwe'</span><span class="token punctuation">)</span><span class="token punctuation">;</span>
<span class="token comment">-- 查询数据</span>
cdh04 :<span class="token punctuation">)</span> <span class="token keyword">SELECT</span> <span class="token operator">*</span> <span class="token keyword">FROM</span> mysql_engine_table<span class="token punctuation">;</span>

<span class="token keyword">SELECT</span> <span class="token operator">*</span>
<span class="token keyword">FROM</span> mysql_engine_table

┌─id─┬─name──┐
│  <span class="token number">1</span> │ tom   │
│  <span class="token number">2</span> │ jack  │
│  <span class="token number">3</span> │ lihua │
└────┴───────┘
<span class="token comment">-- 插入数据，会将数据插入MySQL对应的表中</span>
<span class="token comment">-- 所以当查询MySQL数据时，会发现新增了一条数据</span>
<span class="token keyword">INSERT</span> <span class="token keyword">INTO</span> mysql_engine_table <span class="token keyword">VALUES</span><span class="token punctuation">(</span><span class="token number">4</span><span class="token punctuation">,</span><span class="token string">'robin'</span><span class="token punctuation">)</span><span class="token punctuation">;</span>
<span class="token comment">-- 再次查询</span>
cdh04 :<span class="token punctuation">)</span> <span class="token keyword">select</span> <span class="token operator">*</span> <span class="token keyword">from</span> mysql_engine_table<span class="token punctuation">;</span>                

<span class="token keyword">SELECT</span> <span class="token operator">*</span>
<span class="token keyword">FROM</span> mysql_engine_table

┌─id─┬─name──┐
│  <span class="token number">1</span> │ tom   │
│  <span class="token number">2</span> │ jack  │
│  <span class="token number">3</span> │ lihua │
│  <span class="token number">4</span> │ robin │
└────┴───────┘
</code></pre> 
<p><strong>注意</strong>：对于MySQL表引擎，不支持UPDATE和DELETE操作，比如执行下面命令时，会报错：</p> 
<pre class=" language-sql"><code class="prism  language-sql"><span class="token comment">-- 执行更新</span>
<span class="token keyword">ALTER</span> <span class="token keyword">TABLE</span> mysql_engine_table <span class="token keyword">UPDATE</span> name <span class="token operator">=</span> <span class="token string">'hanmeimei'</span> <span class="token keyword">WHERE</span> id <span class="token operator">=</span> <span class="token number">1</span><span class="token punctuation">;</span>
<span class="token comment">-- 执行删除</span>
<span class="token keyword">ALTER</span> <span class="token keyword">TABLE</span> mysql_engine_table <span class="token keyword">DELETE</span> <span class="token keyword">WHERE</span> id <span class="token operator">=</span> <span class="token number">1</span><span class="token punctuation">;</span>

<span class="token comment">-- 报错</span>
DB::Exception: Mutations are <span class="token operator">not</span> supported <span class="token keyword">by</span> storage MySQL<span class="token punctuation">.</span>
</code></pre> 
<h3><a id="JDBC_1269"></a>JDBC</h3> 
<h4><a id="_1271"></a>使用方式</h4> 
<p>JDBC表引擎不仅可以对接MySQL数据库，还能够与PostgreSQL等数据库。为了实现JDBC连接，ClickHouse使用了<strong>clickhouse-jdbc-bridge</strong>的查询代理服务。</p> 
<p>首先我们需要下载clickhouse-jdbc-bridge，然后按照ClickHouse的github中的步骤进行编译，编译完成之后会有一个<strong>clickhouse-jdbc-bridge-1.0.jar</strong>的jar文件，除了需要该文件之外，还需要JDBC的驱动文件，本文使用的是MySQL，所以还需要下载MySQL驱动包。将MySQL的驱动包和<strong>clickhouse-jdbc-bridge-1.0.jar</strong>文件放在了/opt/softwares路径下，执行如下命令：</p> 
<pre class=" language-bash"><code class="prism  language-bash"><span class="token punctuation">[</span>root@cdh04 softwares<span class="token punctuation">]</span><span class="token comment"># java -jar clickhouse-jdbc-bridge-1.0.jar  --driver-path .  --listen-host cdh04 </span>
</code></pre> 
<p>其中<code>--driver-path</code>是MySQL驱动的jar所在的路径，<code>listen-host</code>是代理服务绑定的主机。默认情况下，绑定的端口是：<strong>9019</strong>。上述jar包的下载：</p> 
<blockquote> 
 <p>链接：https://pan.baidu.com/s/1ZcvF22GvnvAQpVTleNry7Q<br> 提取码：la9n</p> 
</blockquote> 
<p>然后我们再配置<code>/etc/clickhouse-server/config.xml</code>，在文件中添加如下配置，然后重启服务。</p> 
<pre class=" language-xml"><code class="prism  language-xml"><span class="token tag"><span class="token tag"><span class="token punctuation">&lt;</span>jdbc_bridge</span><span class="token punctuation">&gt;</span></span>
    <span class="token tag"><span class="token tag"><span class="token punctuation">&lt;</span>host</span><span class="token punctuation">&gt;</span></span>cdh04<span class="token tag"><span class="token tag"><span class="token punctuation">&lt;/</span>host</span><span class="token punctuation">&gt;</span></span>
    <span class="token tag"><span class="token tag"><span class="token punctuation">&lt;</span>port</span><span class="token punctuation">&gt;</span></span>9019<span class="token tag"><span class="token tag"><span class="token punctuation">&lt;/</span>port</span><span class="token punctuation">&gt;</span></span>
<span class="token tag"><span class="token tag"><span class="token punctuation">&lt;/</span>jdbc_bridge</span><span class="token punctuation">&gt;</span></span>
</code></pre> 
<h4><a id="_1295"></a>使用示例</h4> 
<ul><li>直接查询MySQL中对应的表</li></ul> 
<pre class=" language-sql"><code class="prism  language-sql"><span class="token keyword">SELECT</span> <span class="token operator">*</span> 
<span class="token keyword">FROM</span>
jdbc<span class="token punctuation">(</span>
<span class="token string">'jdbc:mysql://192.168.200.241:3306/?user=root&amp;password=123qwe'</span><span class="token punctuation">,</span> 
<span class="token string">'clickhouse'</span><span class="token punctuation">,</span>
<span class="token string">'test'</span><span class="token punctuation">)</span><span class="token punctuation">;</span>
</code></pre> 
<ul><li>创建一张映射表</li></ul> 
<pre class=" language-sql"><code class="prism  language-sql"><span class="token comment">-- 语法</span>
<span class="token keyword">CREATE</span> <span class="token keyword">TABLE</span> <span class="token punctuation">[</span><span class="token keyword">IF</span> <span class="token operator">NOT</span> <span class="token keyword">EXISTS</span><span class="token punctuation">]</span> <span class="token punctuation">[</span>db<span class="token punctuation">.</span><span class="token punctuation">]</span>table_name
<span class="token punctuation">(</span>
    <span class="token keyword">columns</span> list<span class="token punctuation">.</span><span class="token punctuation">.</span><span class="token punctuation">.</span>
<span class="token punctuation">)</span>
<span class="token keyword">ENGINE</span> <span class="token operator">=</span> JDBC<span class="token punctuation">(</span>dbms_uri<span class="token punctuation">,</span> external_database<span class="token punctuation">,</span> external_table<span class="token punctuation">)</span>

<span class="token comment">-- MySQL建表</span>
<span class="token keyword">CREATE</span> <span class="token keyword">TABLE</span> jdbc_table_mysql <span class="token punctuation">(</span>
  order_id <span class="token keyword">INT</span> <span class="token operator">NOT</span> <span class="token boolean">NULL</span> <span class="token keyword">AUTO_INCREMENT</span><span class="token punctuation">,</span>
  amount <span class="token keyword">FLOAT</span> <span class="token operator">NOT</span> <span class="token boolean">NULL</span><span class="token punctuation">,</span>
<span class="token keyword">PRIMARY</span> <span class="token keyword">KEY</span> <span class="token punctuation">(</span>order_id<span class="token punctuation">)</span><span class="token punctuation">)</span><span class="token punctuation">;</span>
<span class="token keyword">INSERT</span> <span class="token keyword">INTO</span>  jdbc_table_mysql <span class="token keyword">VALUES</span> <span class="token punctuation">(</span><span class="token number">1</span><span class="token punctuation">,</span><span class="token number">200</span><span class="token punctuation">)</span><span class="token punctuation">;</span>

<span class="token comment">-- 在ClickHouse中建表</span>
<span class="token keyword">CREATE</span> <span class="token keyword">TABLE</span> jdbc_table
<span class="token punctuation">(</span>
    order_id Int32<span class="token punctuation">,</span>
    amount Float32
<span class="token punctuation">)</span>
<span class="token keyword">ENGINE</span> JDBC<span class="token punctuation">(</span>
<span class="token string">'jdbc:mysql://192.168.200.241:3306/?user=root&amp;password=123qwe'</span><span class="token punctuation">,</span> 
<span class="token string">'clickhouse'</span><span class="token punctuation">,</span>
<span class="token string">'jdbc_table_mysql'</span><span class="token punctuation">)</span><span class="token punctuation">;</span>

<span class="token comment">-- 查询数据</span>
cdh04 :<span class="token punctuation">)</span> <span class="token keyword">select</span> <span class="token operator">*</span> <span class="token keyword">from</span> jdbc_table<span class="token punctuation">;</span>

<span class="token keyword">SELECT</span> <span class="token operator">*</span>
<span class="token keyword">FROM</span> jdbc_table

┌─order_id─┬─amount─┐
│        <span class="token number">1</span> │    <span class="token number">200</span> │
└──────────┴────────┘
</code></pre> 
<h3><a id="Kafka_1347"></a>Kafka</h3> 
<h4><a id="_1349"></a>使用方式</h4> 
<pre class=" language-sql"><code class="prism  language-sql"><span class="token keyword">CREATE</span> <span class="token keyword">TABLE</span> <span class="token punctuation">[</span><span class="token keyword">IF</span> <span class="token operator">NOT</span> <span class="token keyword">EXISTS</span><span class="token punctuation">]</span> <span class="token punctuation">[</span>db<span class="token punctuation">.</span><span class="token punctuation">]</span>table_name <span class="token punctuation">[</span><span class="token keyword">ON</span> CLUSTER cluster<span class="token punctuation">]</span>
<span class="token punctuation">(</span>
    name1 <span class="token punctuation">[</span>type1<span class="token punctuation">]</span> <span class="token punctuation">[</span><span class="token keyword">DEFAULT</span><span class="token operator">|</span>MATERIALIZED<span class="token operator">|</span>ALIAS expr1<span class="token punctuation">]</span><span class="token punctuation">,</span>
    name2 <span class="token punctuation">[</span>type2<span class="token punctuation">]</span> <span class="token punctuation">[</span><span class="token keyword">DEFAULT</span><span class="token operator">|</span>MATERIALIZED<span class="token operator">|</span>ALIAS expr2<span class="token punctuation">]</span><span class="token punctuation">,</span>
    <span class="token punctuation">.</span><span class="token punctuation">.</span><span class="token punctuation">.</span>
<span class="token punctuation">)</span> <span class="token keyword">ENGINE</span> <span class="token operator">=</span> Kafka<span class="token punctuation">(</span><span class="token punctuation">)</span>
SETTINGS
    kafka_broker_list <span class="token operator">=</span> <span class="token string">'host:port'</span><span class="token punctuation">,</span>
    kafka_topic_list <span class="token operator">=</span> <span class="token string">'topic1,topic2,...'</span><span class="token punctuation">,</span>
    kafka_group_name <span class="token operator">=</span> <span class="token string">'group_name'</span><span class="token punctuation">,</span>
    kafka_format <span class="token operator">=</span> <span class="token string">'data_format'</span><span class="token punctuation">[</span><span class="token punctuation">,</span><span class="token punctuation">]</span>
    <span class="token punctuation">[</span>kafka_row_delimiter <span class="token operator">=</span> <span class="token string">'delimiter_symbol'</span><span class="token punctuation">,</span><span class="token punctuation">]</span>
    <span class="token punctuation">[</span>kafka_schema <span class="token operator">=</span> <span class="token string">''</span><span class="token punctuation">,</span><span class="token punctuation">]</span>
    <span class="token punctuation">[</span>kafka_num_consumers <span class="token operator">=</span> N<span class="token punctuation">,</span><span class="token punctuation">]</span>
    <span class="token punctuation">[</span>kafka_max_block_size <span class="token operator">=</span> <span class="token number">0</span><span class="token punctuation">,</span><span class="token punctuation">]</span>
    <span class="token punctuation">[</span>kafka_skip_broken_messages <span class="token operator">=</span> N<span class="token punctuation">,</span><span class="token punctuation">]</span>
    <span class="token punctuation">[</span>kafka_commit_every_batch <span class="token operator">=</span> <span class="token number">0</span><span class="token punctuation">,</span><span class="token punctuation">]</span>
    <span class="token punctuation">[</span>kafka_thread_per_consumer <span class="token operator">=</span> <span class="token number">0</span><span class="token punctuation">]</span>
</code></pre> 
<ul><li><code>kafka_broker_list</code> ：逗号分隔的brokers地址 (localhost:9092).</li><li><code>kafka_topic_list</code> ：Kafka 主题列表，多个主题用逗号分隔.</li><li><code>kafka_group_name</code> ：消费者组.</li><li><code>kafka_format</code> – Message format. 比如<code>JSONEachRow</code>、JSON、CSV等等</li></ul> 
<h4><a id="_1377"></a>使用示例</h4> 
<p>在kafka中创建ck_topic主题，并向该主题写入数据</p> 
<pre class=" language-sql"><code class="prism  language-sql"> <span class="token keyword">CREATE</span> <span class="token keyword">TABLE</span> kafka_table <span class="token punctuation">(</span>
    id UInt64<span class="token punctuation">,</span>
    name String
  <span class="token punctuation">)</span> <span class="token keyword">ENGINE</span> <span class="token operator">=</span> Kafka<span class="token punctuation">(</span><span class="token punctuation">)</span>
    SETTINGS
    kafka_broker_list <span class="token operator">=</span> <span class="token string">'cdh04:9092'</span><span class="token punctuation">,</span>
    kafka_topic_list <span class="token operator">=</span> <span class="token string">'ck_topic'</span><span class="token punctuation">,</span>
    kafka_group_name <span class="token operator">=</span> <span class="token string">'group1'</span><span class="token punctuation">,</span>
    kafka_format <span class="token operator">=</span> <span class="token string">'JSONEachRow'</span>
<span class="token punctuation">;</span>
<span class="token comment">-- 查询</span>
cdh04 :<span class="token punctuation">)</span> <span class="token keyword">select</span> <span class="token operator">*</span> <span class="token keyword">from</span> kafka_table <span class="token punctuation">;</span>

<span class="token keyword">SELECT</span> <span class="token operator">*</span>
<span class="token keyword">FROM</span> kafka_table

┌─id─┬─name─┐
│  <span class="token number">1</span> │ tom  │
└────┴──────┘
┌─id─┬─name─┐
│  <span class="token number">2</span> │ jack │
└────┴──────┘
</code></pre> 
<h4><a id="_1406"></a>注意点</h4> 
<p>当我们一旦查询完毕之后，ClickHouse会删除表内的数据，其实Kafka表引擎只是一个数据管道，我们可以通过物化视图的方式访问Kafka中的数据。</p> 
<ul><li>首先创建一张Kafka表引擎的表，用于从Kafka中读取数据</li><li>然后再创建一张普通表引擎的表，比如MergeTree，面向终端用户使用</li><li>最后创建物化视图，用于将Kafka引擎表实时同步到终端用户所使用的表中</li></ul> 
<pre class=" language-sql"><code class="prism  language-sql"><span class="token comment">--  创建Kafka引擎表</span>
 <span class="token keyword">CREATE</span> <span class="token keyword">TABLE</span> kafka_table_consumer <span class="token punctuation">(</span>
    id UInt64<span class="token punctuation">,</span>
    name String
  <span class="token punctuation">)</span> <span class="token keyword">ENGINE</span> <span class="token operator">=</span> Kafka<span class="token punctuation">(</span><span class="token punctuation">)</span>
    SETTINGS
    kafka_broker_list <span class="token operator">=</span> <span class="token string">'cdh04:9092'</span><span class="token punctuation">,</span>
    kafka_topic_list <span class="token operator">=</span> <span class="token string">'ck_topic'</span><span class="token punctuation">,</span>
    kafka_group_name <span class="token operator">=</span> <span class="token string">'group1'</span><span class="token punctuation">,</span>
    kafka_format <span class="token operator">=</span> <span class="token string">'JSONEachRow'</span>
<span class="token punctuation">;</span>

<span class="token comment">-- 创建一张终端用户使用的表</span>
<span class="token keyword">CREATE</span> <span class="token keyword">TABLE</span> kafka_table_mergetree <span class="token punctuation">(</span>
  id UInt64 <span class="token punctuation">,</span>
  name String
  <span class="token punctuation">)</span><span class="token keyword">ENGINE</span><span class="token operator">=</span>MergeTree<span class="token punctuation">(</span><span class="token punctuation">)</span>
  <span class="token keyword">ORDER</span> <span class="token keyword">BY</span> id
  <span class="token punctuation">;</span>
  
<span class="token comment">-- 创建物化视图，同步数据</span>
<span class="token keyword">CREATE</span> MATERIALIZED <span class="token keyword">VIEW</span> consumer <span class="token keyword">TO</span> kafka_table_mergetree
    <span class="token keyword">AS</span> <span class="token keyword">SELECT</span> id<span class="token punctuation">,</span>name <span class="token keyword">FROM</span> kafka_table_consumer <span class="token punctuation">;</span>
<span class="token comment">-- 查询，多次查询，已经被查询的数据依然会被输出</span>
cdh04 :<span class="token punctuation">)</span> <span class="token keyword">select</span> <span class="token operator">*</span> <span class="token keyword">from</span> kafka_table_mergetree<span class="token punctuation">;</span>

<span class="token keyword">SELECT</span> <span class="token operator">*</span>
<span class="token keyword">FROM</span> kafka_table_mergetree

┌─id─┬─name─┐
│  <span class="token number">2</span> │ jack │
└────┴──────┘
┌─id─┬─name─┐
│  <span class="token number">1</span> │ tom  │
└────┴──────┘
</code></pre> 
<h2><a id="_1452"></a>其他特殊的表引擎</h2> 
<h3><a id="Memory_1454"></a>Memory表引擎</h3> 
<p>Memory表引擎直接将数据保存在内存中，数据既不会被压缩也不会被格式转换。当ClickHouse服务重启的时候，Memory表内的数据会全部丢失。一般在测试时使用。</p> 
<pre class=" language-sql"><code class="prism  language-sql"> <span class="token keyword">CREATE</span> <span class="token keyword">TABLE</span> table_memory <span class="token punctuation">(</span>
    id UInt64<span class="token punctuation">,</span>
    name String
  <span class="token punctuation">)</span> <span class="token keyword">ENGINE</span> <span class="token operator">=</span> Memory<span class="token punctuation">(</span><span class="token punctuation">)</span><span class="token punctuation">;</span>
</code></pre> 
<h3><a id="Distributed_1465"></a>Distributed表引擎</h3> 
<h4><a id="_1467"></a>使用方式</h4> 
<p>Distributed表引擎是分布式表的代名词，它自身不存储任何数据，数据都分散存储在某一个分片上，能够自动路由数据至集群中的各个节点，所以Distributed表引擎需要和其他数据表引擎一起协同工作。</p> 
<p>所以，一张分布式表底层会对应多个本地分片数据表，由具体的分片表存储数据，分布式表与分片表是<strong>一对多的关系</strong></p> 
<p><strong>Distributed表引擎的定义形式如下所示</strong></p> 
<pre class=" language-sql"><code class="prism  language-sql"><span class="token keyword">Distributed</span><span class="token punctuation">(</span>cluster_name<span class="token punctuation">,</span> database_name<span class="token punctuation">,</span> table_name<span class="token punctuation">[</span><span class="token punctuation">,</span> sharding_key<span class="token punctuation">]</span><span class="token punctuation">)</span>
</code></pre> 
<p>各个参数的含义分别如下：</p> 
<ul><li><strong>cluster_name</strong>：集群名称，与集群配置中的自定义名称相对应。</li><li><strong>database_name</strong>：数据库名称</li><li><strong>table_name</strong>：表名称</li><li><strong>sharding_key</strong>：可选的，用于分片的key值，在数据写入的过程中，分布式表会依据分片key的规则，将数据分布到各个节点的本地表。</li></ul> 
<blockquote> 
 <p>尖叫提示：</p> 
 <p>创建分布式表是<strong>读时检查的机制</strong>，也就是说对<strong>创建分布式表和本地表的顺序并没有强制要求</strong>。</p> 
 <p>同样值得注意的是，在上面的语句中使用了ON CLUSTER分布式DDL，这意味着在集群的每个分片节点上，都会创建一张Distributed表，这样便可以从其中任意一端发起对所有分片的读、写请求。</p> 
</blockquote> 
<h4><a id="_1492"></a>使用示例</h4> 
<pre class=" language-sql"><code class="prism  language-sql"><span class="token comment">-- 创建一张分布式表</span>
<span class="token keyword">CREATE</span> <span class="token keyword">TABLE</span> <span class="token keyword">IF</span> <span class="token operator">NOT</span> <span class="token keyword">EXISTS</span> user_cluster <span class="token keyword">ON</span> CLUSTER cluster_3shards_1replicas
<span class="token punctuation">(</span>
    id Int32<span class="token punctuation">,</span>
    name String
<span class="token punctuation">)</span><span class="token keyword">ENGINE</span> <span class="token operator">=</span> <span class="token keyword">Distributed</span><span class="token punctuation">(</span>cluster_3shards_1replicas<span class="token punctuation">,</span> <span class="token keyword">default</span><span class="token punctuation">,</span> user_local<span class="token punctuation">,</span>id<span class="token punctuation">)</span><span class="token punctuation">;</span>

</code></pre> 
<p>创建完成上面的分布式表时，在每台机器上查看表，发现每台机器上都存在一张刚刚创建好的表。</p> 
<p>接下来就需要创建本地表了，在每台机器上分别创建一张本地表：</p> 
<pre class=" language-sql"><code class="prism  language-sql"><span class="token keyword">CREATE</span> <span class="token keyword">TABLE</span> <span class="token keyword">IF</span> <span class="token operator">NOT</span> <span class="token keyword">EXISTS</span> user_local 
<span class="token punctuation">(</span>
    id Int32<span class="token punctuation">,</span>
    name String
<span class="token punctuation">)</span><span class="token keyword">ENGINE</span> <span class="token operator">=</span> MergeTree<span class="token punctuation">(</span><span class="token punctuation">)</span>
<span class="token keyword">ORDER</span> <span class="token keyword">BY</span> id
<span class="token keyword">PARTITION</span> <span class="token keyword">BY</span> id
<span class="token keyword">PRIMARY</span> <span class="token keyword">KEY</span> id<span class="token punctuation">;</span>
</code></pre> 
<p>我们先在一台机器上，对user_local表进行插入数据，然后再查询user_cluster表</p> 
<pre class=" language-sql"><code class="prism  language-sql"><span class="token comment">-- 插入数据</span>
cdh04 :<span class="token punctuation">)</span> <span class="token keyword">INSERT</span> <span class="token keyword">INTO</span> user_local <span class="token keyword">VALUES</span><span class="token punctuation">(</span><span class="token number">1</span><span class="token punctuation">,</span><span class="token string">'tom'</span><span class="token punctuation">)</span><span class="token punctuation">,</span><span class="token punctuation">(</span><span class="token number">2</span><span class="token punctuation">,</span><span class="token string">'jack'</span><span class="token punctuation">)</span><span class="token punctuation">;</span>
<span class="token comment">-- 查询user_cluster表,可见通过user_cluster表可以操作所有的user_local表</span>
cdh04 :<span class="token punctuation">)</span> <span class="token keyword">select</span> <span class="token operator">*</span> <span class="token keyword">from</span> user_cluster<span class="token punctuation">;</span>
┌─id─┬─name─┐
│  <span class="token number">2</span> │ jack │
└────┴──────┘
┌─id─┬─name─┐
│  <span class="token number">1</span> │ tom  │
└────┴──────┘
</code></pre> 
<p>接下来，我们再向user_cluster中插入一些数据，观察user_local表数据变化，可以发现数据被分散存储到了其他节点上了。</p> 
<pre class=" language-sql"><code class="prism  language-sql"><span class="token comment">-- 向user_cluster插入数据</span>
cdh04 :<span class="token punctuation">)</span>  <span class="token keyword">INSERT</span> <span class="token keyword">INTO</span> user_cluster <span class="token keyword">VALUES</span><span class="token punctuation">(</span><span class="token number">3</span><span class="token punctuation">,</span><span class="token string">'lilei'</span><span class="token punctuation">)</span><span class="token punctuation">,</span><span class="token punctuation">(</span><span class="token number">4</span><span class="token punctuation">,</span><span class="token string">'lihua'</span><span class="token punctuation">)</span><span class="token punctuation">;</span> 
<span class="token comment">-- 查看user_cluster数据</span>
cdh04 :<span class="token punctuation">)</span> <span class="token keyword">select</span> <span class="token operator">*</span> <span class="token keyword">from</span> user_cluster<span class="token punctuation">;</span>
┌─id─┬─name─┐
│  <span class="token number">2</span> │ jack │
└────┴──────┘
┌─id─┬─name──┐
│  <span class="token number">3</span> │ lilei │
└────┴───────┘
┌─id─┬─name─┐
│  <span class="token number">1</span> │ tom  │
└────┴──────┘
┌─id─┬─name──┐
│  <span class="token number">4</span> │ lihua │
└────┴───────┘

<span class="token comment">-- 在cdh04上查看user_local</span>
cdh04 :<span class="token punctuation">)</span> <span class="token keyword">select</span> <span class="token operator">*</span> <span class="token keyword">from</span> user_local<span class="token punctuation">;</span>
┌─id─┬─name─┐
│  <span class="token number">2</span> │ jack │
└────┴──────┘
┌─id─┬─name──┐
│  <span class="token number">3</span> │ lilei │
└────┴───────┘
┌─id─┬─name─┐
│  <span class="token number">1</span> │ tom  │
└────┴──────┘
<span class="token comment">-- 在cdh05上查看user_local</span>
cdh05 :<span class="token punctuation">)</span> <span class="token keyword">select</span> <span class="token operator">*</span> <span class="token keyword">from</span> user_local<span class="token punctuation">;</span>
┌─id─┬─name──┐
│  <span class="token number">4</span> │ lihua │
└────┴───────┘
</code></pre> 
<h2><a id="_1572"></a>总结</h2> 
<p>ClickHouse提供了非常多的表引擎，每一种表引擎都有各自的适用场景。通过特定的表引擎支撑特定的场景，十分灵活。本文主要分享了ClickHouse提供的常见表引擎，并对每种表引擎给出了适用场景和使用示例，希望对你有所帮助。</p> 
<blockquote> 
 <p>公众号『大数据技术与数仓』，回复『资料』领取大数据资料包</p> 
</blockquote>
                
</div>
<div id="MySignature" style="display: block;">公众号「大数据技术与数仓」
专注分享数据仓库与大数据技术</div>
<div class="clear"></div>
<div id="blog_post_info_block">


    <div id="blog_post_info">
<div id="green_channel">
        <a href="javascript:void(0);" id="green_channel_digg" onclick="DiggIt(13708834,cb_blogId,1);green_channel_success(this,&#39;谢谢推荐！&#39;);">好文要顶</a>
        <a id="green_channel_follow" onclick="follow(&#39;3c36af46-dcf7-412c-561c-08d838384ec2&#39;);" href="javascript:void(0);">关注我</a>
    <a id="green_channel_favorite" onclick="AddToWz(cb_entryId);return false;" href="javascript:void(0);">收藏该文</a>
    <a id="green_channel_weibo" href="javascript:void(0);" title="分享至新浪微博" onclick="ShareToTsina()"><img src="./篇二_什么是ClickHouse的表引擎_ - 大数据技术与数仓 - 博客园_files/icon_weibo_24.png" alt=""></a>
    <a id="green_channel_wechat" href="javascript:void(0);" title="分享至微信" onclick="shareOnWechat()"><img src="./篇二_什么是ClickHouse的表引擎_ - 大数据技术与数仓 - 博客园_files/wechat.png" alt=""></a>
</div>
<div id="author_profile">
    <div id="author_profile_info" class="author_profile_info">
            <a href="https://home.cnblogs.com/u/jmx-bigdata/" target="_blank"><img src="./篇二_什么是ClickHouse的表引擎_ - 大数据技术与数仓 - 博客园_files/20200807094348.png" class="author_avatar" alt=""></a>
        <div id="author_profile_detail" class="author_profile_info">
            <a href="https://home.cnblogs.com/u/jmx-bigdata/">大数据技术与数仓</a><br>
            <a href="https://home.cnblogs.com/u/jmx-bigdata/followees/">关注 - 0</a><br>
            <a href="https://home.cnblogs.com/u/jmx-bigdata/followers/">粉丝 - 8</a>
        </div>
    </div>
    <div class="clear"></div>
    <div id="author_profile_honor"></div>
    <div id="author_profile_follow">
                <a href="javascript:void(0);" onclick="follow(&#39;3c36af46-dcf7-412c-561c-08d838384ec2&#39;);return false;">+加关注</a>
    </div>
</div>
<div id="div_digg">
    <div class="diggit" onclick="votePost(13708834,&#39;Digg&#39;)">
        <span class="diggnum" id="digg_count">0</span>
    </div>
    <div class="buryit" onclick="votePost(13708834,&#39;Bury&#39;)">
        <span class="burynum" id="bury_count">0</span>
    </div>
    <div class="clear"></div>
    <div class="diggword" id="digg_tips">
    </div>
</div>

<script type="text/javascript">
    currentDiggType = 0;
</script></div>
    <div class="clear"></div>
    <div id="post_next_prev">

    <a href="https://www.cnblogs.com/jmx-bigdata/p/13708835.html" class="p_n_p_prefix">« </a> 上一篇：    <a href="https://www.cnblogs.com/jmx-bigdata/p/13708835.html" title="发布于 2020-09-13 21:27">篇一|ClickHouse快速入门</a>
    <br>
    <a href="https://www.cnblogs.com/jmx-bigdata/p/14021186.html" class="p_n_p_prefix">» </a> 下一篇：    <a href="https://www.cnblogs.com/jmx-bigdata/p/14021186.html" title="发布于 2020-10-14 21:32">篇三|ClickHouse的数据类型</a>

</div>
</div>
            </div>
            <div class="postDesc">posted @ 
<span id="post-date">2020-09-21 21:38</span>&nbsp;
<a href="https://www.cnblogs.com/jmx-bigdata/">大数据技术与数仓</a>&nbsp;
阅读(<span id="post_view_count">1951</span>)&nbsp;
评论(<span id="post_comment_count">0</span>)&nbsp;
<a href="https://i.cnblogs.com/EditPosts.aspx?postid=13708834" rel="nofollow">编辑</a>&nbsp;
<a href="javascript:void(0)" onclick="AddToWz(13708834);return false;">收藏</a>&nbsp;
<a href="javascript:void(0)" onclick="reportManager.report({ currentUserId: &#39;&#39;, targetType: &#39;blogPost&#39;, targetId: &#39;13708834&#39;, targetLink: &#39;https://www.cnblogs.com/jmx-bigdata/p/13708834.html&#39;, title: &#39;篇二|什么是ClickHouse的表引擎?&#39; })">举报</a></div>
        </div>
	    
	    
    </div><!--end: topics 文章、评论容器-->
</div>
<script src="./篇二_什么是ClickHouse的表引擎_ - 大数据技术与数仓 - 博客园_files/highlight.min.js.下载"></script>
<script>markdown_highlight();</script>
<script>
    var allowComments = true, cb_blogId = 622219, cb_blogApp = 'jmx-bigdata', cb_blogUserGuid = '3c36af46-dcf7-412c-561c-08d838384ec2';
    var cb_entryId = 13708834, cb_entryCreatedDate = '2020-09-21 21:38', cb_postType = 1;
    updatePostStats(
        [cb_entryId],
        function(id, count) { $("#post_view_count").text(count) },
        function(id, count) { $("#post_comment_count").text(count) })
    zoomManager.apply("#cnblogs_post_body img:not(.code_img_closed):not(.code_img_opened)");
</script>
<a name="!comments"></a>
<div id="blog-comments-placeholder"></div>
<div id="comment_form" class="commentform">
    <a name="commentform"></a>
    <div id="divCommentShow"></div>
    <div id="comment_nav"><span id="span_refresh_tips"></span><a href="javascript:void(0);" onclick="return RefreshCommentList();" id="lnk_RefreshComments" runat="server" clientidmode="Static">刷新评论</a><a href="https://www.cnblogs.com/jmx-bigdata/p/13708834.html#" onclick="return RefreshPage();">刷新页面</a><a href="https://www.cnblogs.com/jmx-bigdata/p/13708834.html#top">返回顶部</a></div>
    <div id="comment_form_container" style="visibility: visible;"><div class="login_tips">
    登录后才能查看或发表评论，立即 <a rel="nofollow" href="javascript:void(0);" class="underline" onclick="return account.login(&#39;!comments&#39;);">登录</a> 或者
    <a href="https://www.cnblogs.com/">逛逛</a> 博客园首页
</div>
</div>
    <div class="ad_text_commentbox" id="ad_text_under_commentbox"></div>
    <div id="ad_t2"><a href="https://developer.aliyun.com/topic/n2021?channel=yy_developer?utm_content=g_1000278324" target="_blank" onclick="ga(&#39;send&#39;, &#39;event&#39;, &#39;Link&#39;, &#39;click&#39;, &#39;T2-阿里云-研发效能峰会&#39;)">【推荐】因云而生，生而敏捷：2021阿里巴巴研发效能峰会，邀你共享效能盛宴</a><br><a href="https://cloud.baidu.com/campaign/2021mid/index.html?track=cp:bokeyuan|pf:pc|pp:bokeyuan-huodong-21nianzhongdacu-neiyewenzilian|pu:21nianzhongdacu-neiyewenzilian|ci:21nzdc|kw:10225193" target="_blank" onclick="ga(&#39;send&#39;, &#39;event&#39;, &#39;Link&#39;, &#39;click&#39;, &#39;T2-百度智能云&#39;)">【推荐】百度智能云618年中大促，限时抢购，新老用户同享超值折扣</a><br><a href="http://www.uccpsoft.com/index.htm" target="_blank" onclick="ga(&#39;send&#39;, &#39;event&#39;, &#39;Link&#39;, &#39;click&#39;, &#39;T2-ucancode&#39;)">【推荐】大型组态、工控、仿真、CAD\GIS 50万行VC++源码免费下载!</a><br><a href="https://brands.cnblogs.com/huawei" target="_blank" onclick="ga(&#39;send&#39;, &#39;event&#39;, &#39;Link&#39;, &#39;click&#39;, &#39;T2-华为专区&#39;)">【推荐】618好物推荐：基于HarmonyOS和小熊派BearPi-HM Nano的护花使者</a><br><a href="https://www.aliyun.com/activity/daily/bestoffer?userCode=swh7dvlt" target="_blank" onclick="ga(&#39;send&#39;, &#39;event&#39;, &#39;Link&#39;, &#39;click&#39;, &#39;T2-阿里云-cps&#39;)">【推荐】阿里云爆品销量榜单出炉，精选爆款产品低至0.55折</a><br></div>
    <div id="opt_under_post"></div>
    <div id="cnblogs_c1" class="under-post-card">
        <div id="div-gpt-ad-1592365906576-0" style="width: 300px; height: 250px;" data-google-query-id="CO7OmP__r_ECFVMOvQodrd4O4Q"><div id="google_ads_iframe_/1090369/C1_0__container__" style="border: 0pt none;"><iframe id="google_ads_iframe_/1090369/C1_0" title="3rd party ad content" name="google_ads_iframe_/1090369/C1_0" width="300" height="250" scrolling="no" marginwidth="0" marginheight="0" frameborder="0" sandbox="allow-forms allow-popups allow-popups-to-escape-sandbox allow-same-origin allow-scripts allow-top-navigation-by-user-activation" allow="conversion-measurement" style="border: 0px; vertical-align: bottom;" data-google-container-id="1" data-load-complete="true" src="./篇二_什么是ClickHouse的表引擎_ - 大数据技术与数仓 - 博客园_files/saved_resource.html"></iframe></div></div>
    </div>
    <div id="under_post_card1"><div class="under-post-card">
<b>编辑推荐：</b>
<br>

· <a href="https://www.cnblogs.com/coco1s/p/14921479.html" target="_blank">Web 动画原则及技巧浅析</a>
    <br>
· <a href="https://www.cnblogs.com/yexiaochai/p/14915151.html" target="_blank">技术管理进阶 —— 技术总监的第一要务</a>
    <br>
· <a href="https://www.cnblogs.com/strick/p/14422539.html" target="_blank">带团队后的日常（二）</a>
    <br>
· <a href="https://www.cnblogs.com/ihardcoder/p/14892276.html" target="_blank">WebGIS vs WebGL 图形编程</a>
    <br>
· <a href="https://www.cnblogs.com/kklldog/p/netcore-with-microservices-04.html" target="_blank">.Net Core with 微服务 - Consul 注册中心</a>
    <br>
</div></div>
    <div id="cnblogs_c2" class="under-post-card">
        <div id="div-gpt-ad-1592366332455-0" style="width: 468px; height: 60px;" data-google-query-id="CNvll___r_ECFVMOvQodrd4O4Q"><div id="google_ads_iframe_/1090369/C2_0__container__" style="border: 0pt none;"><iframe id="google_ads_iframe_/1090369/C2_0" title="3rd party ad content" name="google_ads_iframe_/1090369/C2_0" width="468" height="60" scrolling="no" marginwidth="0" marginheight="0" frameborder="0" allow="conversion-measurement" style="border: 0px; vertical-align: bottom;" data-google-container-id="2" data-load-complete="true" src="./篇二_什么是ClickHouse的表引擎_ - 大数据技术与数仓 - 博客园_files/saved_resource(1).html"></iframe></div></div>
    </div>
    <div id="under_post_card2"><div class="itnews under-post-card">
    <b>最新新闻</b>：
    <br>
 ·          <a href="https://news.cnblogs.com/n/696693/" target="_blank">互联网货运众生相：横向满帮，纵向福佑</a>
        <br>
 ·          <a href="https://news.cnblogs.com/n/696692/" target="_blank">鞋圈泡沫破了：币圈玩家炒鞋血亏50万，有人囤几千双滞销</a>
        <br>
 ·          <a href="https://news.cnblogs.com/n/696691/" target="_blank">特斯拉AI总监揭秘纯视觉系统：雷达只是拐杖</a>
        <br>
 ·          <a href="https://news.cnblogs.com/n/696690/" target="_blank">贝索斯游太空风险太高 保险公司不敢为其投保？</a>
        <br>
 ·          <a href="https://news.cnblogs.com/n/696689/" target="_blank">Apple Watch 7系列可能采用更小的双面结构S7芯片</a>
        <br>
    » <a href="https://news.cnblogs.com/" title="IT 新闻" target="_blank">更多新闻...</a>
</div></div>
    <div id="HistoryToday" class="under-post-card"></div>
    <script type="text/javascript">
       var commentManager = new blogCommentManager();
       commentManager.renderComments(0);
       fixPostBody();
       deliverBigBanner();
setTimeout(function() { incrementViewCount(cb_entryId); }, 50);       deliverT2();
       deliverC1C2();
       loadNewsAndKb();
       loadBlogSignature();
LoadPostCategoriesTags(cb_blogId, cb_entryId);       LoadPostInfoBlock(cb_blogId, cb_entryId, cb_blogApp, cb_blogUserGuid);
       GetPrevNextPost(cb_entryId, cb_blogId, cb_entryCreatedDate, cb_postType);
       loadOptUnderPost();
       GetHistoryToday(cb_blogId, cb_blogApp, cb_entryCreatedDate);
    </script>
</div>

	</div><!--end: forFlow -->
	</div><!--end: mainContent 主体内容容器-->
	<div id="sideBar">
		<div id="sideBarMain">
			<div id="sidebar_news" class="newsItem"><!--done-->
<h3 class="catListTitle">公告</h3>

<div id="blog-news">
    
    <div id="profile_block">
        昵称：
        <a href="https://home.cnblogs.com/u/jmx-bigdata/">
            大数据技术与数仓
        </a>
        <br>
        园龄：
        <a href="https://home.cnblogs.com/u/jmx-bigdata/" title="入园时间：2020-08-07">
            10个月
        </a>
        <br>
        粉丝：
        <a href="https://home.cnblogs.com/u/jmx-bigdata/followers/">
            8
        </a>
        <br>
        关注：
        <a href="https://home.cnblogs.com/u/jmx-bigdata/followees/">
            0
        </a>
        <div id="p_b_follow">
<a href="javascript:void(0)" onclick="follow(&#39;3c36af46-dcf7-412c-561c-08d838384ec2&#39;)">+加关注</a></div>
        <script>getFollowStatus('3c36af46-dcf7-412c-561c-08d838384ec2');</script>
    </div>
</div>

</div>
<div id="sidebar_c3"></div>
			<div id="blog-calendar" style="">

<table id="blogCalendar" class="Cal" cellspacing="0" cellpadding="0" title="Calendar" border="0">
    <tbody>
        <tr>
            <td colspan="7">
                <table class="CalTitle" cellspacing="0" border="0">
                    <tbody>
                        <tr>
                            <td class="CalNextPrev">
                                <a href="javascript:void(0);" onclick="loadBlogCalendar(&#39;2021/05/24&#39;); return false;">&lt;</a>
                            </td>
                            <td align="center">2021年6月</td>
                            <td align="right" class="CalNextPrev">
                                <a href="javascript:void(0);" onclick="loadBlogCalendar(&#39;2021/07/24&#39;); return false;">&gt;</a>
                            </td>
                        </tr>
                    </tbody>
                </table>
            </td>
        </tr>
    <tr>
        <th class="CalDayHeader" align="center" abbr="日" scope="col">日</th>
        <th class="CalDayHeader" align="center" abbr="一" scope="col">一</th>
        <th class="CalDayHeader" align="center" abbr="二" scope="col">二</th>
        <th class="CalDayHeader" align="center" abbr="三" scope="col">三</th>
        <th class="CalDayHeader" align="center" abbr="四" scope="col">四</th>
        <th class="CalDayHeader" align="center" abbr="五" scope="col">五</th>
        <th class="CalDayHeader" align="center" abbr="六" scope="col">六</th>
    </tr>
            <tr>
                            <td class="CalOtherMonthDay" align="center">30</td>
                            <td class="CalOtherMonthDay" align="center">31</td>
                        <td class="" align="center">
                            1
                        </td>
                        <td class="" align="center">
                            2
                        </td>
                        <td class="" align="center">
                            3
                        </td>
                        <td class="" align="center">
                            4
                        </td>
                    <td class="CalWeekendDay" align="center">
                        5
                    </td>
            </tr>
                <tr>
                        <td class="CalWeekendDay" align="center">
                            6
                        </td>
                            <td class="" align="center">
                                7
                            </td>
                            <td class="" align="center">
                                8
                            </td>
                            <td class="" align="center">
                                9
                            </td>
                            <td class="" align="center">
                                10
                            </td>
                            <td class="" align="center">
                                11
                            </td>
                        <td class="CalWeekendDay" align="center">
                            12
                        </td>
                </tr>
                <tr>
                        <td class="CalWeekendDay" align="center">
                            13
                        </td>
                            <td class="" align="center">
                                14
                            </td>
                            <td class="" align="center">
                                15
                            </td>
                            <td class="" align="center">
                                16
                            </td>
                            <td class="" align="center">
                                17
                            </td>
                            <td class="" align="center">
                                18
                            </td>
                        <td class="CalWeekendDay" align="center">
                            19
                        </td>
                </tr>
                <tr>
                        <td class="CalWeekendDay" align="center">
                            20
                        </td>
                            <td class="" align="center">
                                21
                            </td>
                            <td class="" align="center">
                                22
                            </td>
                            <td class="" align="center">
                                23
                            </td>
                            <td class="CalTodayDay" align="center">
                                24
                            </td>
                            <td class="" align="center">
                                25
                            </td>
                        <td class="CalWeekendDay" align="center">
                            26
                        </td>
                </tr>
                <tr>
                        <td class="CalWeekendDay" align="center">
                            27
                        </td>
                            <td class="" align="center">
                                28
                            </td>
                            <td class="" align="center">
                                29
                            </td>
                            <td class="" align="center">
                                30
                            </td>
                            <td class="CalOtherMonthDay" align="center">
                                1
                            </td>
                            <td class="CalOtherMonthDay" align="center">
                                2
                            </td>
                        <td class="CalOtherMonthDay" align="center">
                            3
                        </td>
                </tr>
                <tr>
                        <td class="CalOtherMonthDay" align="center">
                            4
                        </td>
                            <td class="CalOtherMonthDay" align="center">
                                5
                            </td>
                            <td class="CalOtherMonthDay" align="center">
                                6
                            </td>
                            <td class="CalOtherMonthDay" align="center">
                                7
                            </td>
                            <td class="CalOtherMonthDay" align="center">
                                8
                            </td>
                            <td class="CalOtherMonthDay" align="center">
                                9
                            </td>
                        <td class="CalOtherMonthDay" align="center">
                            10
                        </td>
                </tr>
    </tbody>
</table></div><script>loadBlogDefaultCalendar();</script>			
			<div id="leftcontentcontainer">
				<div id="blog-sidecolumn"><!-- 搜索 -->
<div id="sidebar_search" class="sidebar-block">
    <div id="sidebar_search" class="mySearch">
        <h3 class="catListTitle">搜索</h3>
        <div id="sidebar_search_box">
            <div id="widget_my_zzk" class="div_my_zzk">
                <input type="text" id="q" onkeydown="return zzk_go_enter(event);" class="input_my_zzk">&nbsp;<input onclick="zzk_go()" type="button" value="找找看" id="btnZzk" class="btn_my_zzk">
            </div>
            <div id="widget_my_google" class="div_my_zzk">
                <input type="text" name="google_q" id="google_q" onkeydown="return google_go_enter(event);" class="input_my_zzk">&nbsp;<input onclick="google_go()" type="button" value="谷歌搜索" class="btn_my_zzk">
            </div>
        </div>
    </div>
</div>

<!-- 常用链接 -->
<div id="sidebar_shortcut" class="sidebar-block"><div class="catListLink">
<h3 class="catListTitle">
常用链接
</h3>
<ul>
    
<li><a href="https://www.cnblogs.com/jmx-bigdata/p/" title="我的博客的随笔列表">我的随笔</a></li>
<li><a href="https://www.cnblogs.com/jmx-bigdata/MyComments.html" title="我的发表过的评论列表">我的评论</a></li>
<li><a href="https://www.cnblogs.com/jmx-bigdata/OtherPosts.html" title="我评论过的随笔列表">我的参与</a></li>
<li><a href="https://www.cnblogs.com/jmx-bigdata/RecentComments.html" title="我的博客的评论列表">最新评论</a></li>
<li><a href="https://www.cnblogs.com/jmx-bigdata/tag/" title="我的博客的标签列表">我的标签</a></li>

</ul>
</div>

</div>

<!-- 最新随笔 -->


<!-- 我的标签 -->
<div id="sidebar_toptags" class="sidebar-block"><div class="catListTag">
<h3 class="catListTitle">我的标签</h3>
<ul>
        <li>
            <a href="https://www.cnblogs.com/jmx-bigdata/tag/Flink/">Flink<span class="tag-count">(1)</span></a>
        </li>
        <li>
            <a href="https://www.cnblogs.com/jmx-bigdata/tag/SQL/">SQL<span class="tag-count">(1)</span></a>
        </li>
        <li>
            <a href="https://www.cnblogs.com/jmx-bigdata/tag/%E6%95%B0%E4%BB%93/">数仓<span class="tag-count">(1)</span></a>
        </li>
        <li>
            <a href="https://www.cnblogs.com/jmx-bigdata/tag/%E9%9D%A2%E8%AF%95/">面试<span class="tag-count">(1)</span></a>
        </li>
    

</ul>
</div>

</div>

<!-- 积分与排名 -->


<!-- 随笔分类、随笔档案、文章分类、新闻分类、相册、链接 -->
<div id="sidebar_categories">

    <div id="sidebar_postarchive" class="catListPostArchive sidebar-block">
        <h3 class="catListTitle">
            
随笔档案



        </h3>

        <ul>

                <li data-category-list-item-visible="true" style="display: block">
                    
<a href="https://www.cnblogs.com/jmx-bigdata/archive/2021/01.html" rel="" target="">
    2021年1月(1)
</a>
 

                </li>                
                <li data-category-list-item-visible="true" style="display: block">
                    
<a href="https://www.cnblogs.com/jmx-bigdata/archive/2020/12.html" rel="" target="">
    2020年12月(2)
</a>
 

                </li>                
                <li data-category-list-item-visible="true" style="display: block">
                    
<a href="https://www.cnblogs.com/jmx-bigdata/archive/2020/11.html" rel="" target="">
    2020年11月(5)
</a>
 

                </li>                
                <li data-category-list-item-visible="true" style="display: block">
                    
<a href="https://www.cnblogs.com/jmx-bigdata/archive/2020/10.html" rel="" target="">
    2020年10月(2)
</a>
 

                </li>                
                <li data-category-list-item-visible="true" style="display: block">
                    
<a href="https://www.cnblogs.com/jmx-bigdata/archive/2020/09.html" rel="" target="">
    2020年9月(5)
</a>
 

                </li>                
                <li data-category-list-item-visible="true" style="display: block">
                    
<a href="https://www.cnblogs.com/jmx-bigdata/archive/2020/08.html" rel="" target="">
    2020年8月(27)
</a>
 

                </li>                
                <li data-category-list-item-visible="true" style="display: block">
                    
<a href="https://www.cnblogs.com/jmx-bigdata/archive/2020/07.html" rel="" target="">
    2020年7月(17)
</a>
 

                </li>                
                <li data-category-list-item-visible="true" style="display: block">
                    
<a href="https://www.cnblogs.com/jmx-bigdata/archive/2020/06.html" rel="" target="">
    2020年6月(10)
</a>
 

                </li>                
                <li data-category-list-item-visible="true" style="display: block">
                    
<a href="https://www.cnblogs.com/jmx-bigdata/archive/2020/05.html" rel="" target="">
    2020年5月(12)
</a>
 

                </li>                
                <li data-category-list-item-visible="true" style="display: block">
                    
<a href="https://www.cnblogs.com/jmx-bigdata/archive/2020/04.html" rel="" target="">
    2020年4月(13)
</a>
 

                </li>                
                <li data-category-list-item-visible="true" style="display: block">
                    
<a href="https://www.cnblogs.com/jmx-bigdata/archive/2020/03.html" rel="" target="">
    2020年3月(9)
</a>
 

                </li>                
                <li data-category-list-item-visible="true" style="display: block">
                    
<a href="https://www.cnblogs.com/jmx-bigdata/archive/2019/12.html" rel="" target="">
    2019年12月(1)
</a>
 

                </li>                
                <li data-category-list-item-visible="true" style="display: block">
                    
<a href="https://www.cnblogs.com/jmx-bigdata/archive/2019/11.html" rel="" target="">
    2019年11月(1)
</a>
 

                </li>                
                <li data-category-list-item-visible="true" style="display: block">
                    
<a href="https://www.cnblogs.com/jmx-bigdata/archive/2019/10.html" rel="" target="">
    2019年10月(2)
</a>
 

                </li>                
                <li data-category-list-item-visible="true" style="display: block">
                    
<a href="https://www.cnblogs.com/jmx-bigdata/archive/2019/08.html" rel="" target="">
    2019年8月(11)
</a>
 

                </li>                
                <li data-category-list-item-visible="true" style="display: block">
                    
<a href="https://www.cnblogs.com/jmx-bigdata/archive/2019/07.html" rel="" target="">
    2019年7月(6)
</a>
 

                </li>                
                <li data-category-list-item-visible="true" style="display: block">
                    
<a href="https://www.cnblogs.com/jmx-bigdata/archive/2019/03.html" rel="" target="">
    2019年3月(14)
</a>
 

                </li>                
                <li data-category-list-item-visible="true" style="display: block">
                    
<a href="https://www.cnblogs.com/jmx-bigdata/archive/2019/02.html" rel="" target="">
    2019年2月(3)
</a>
 

                </li>                
                <li data-category-list-item-visible="true" style="display: block">
                    
<a href="https://www.cnblogs.com/jmx-bigdata/archive/2018/12.html" rel="" target="">
    2018年12月(4)
</a>
 

                </li>                
                <li data-category-list-item-visible="true" style="display: block">
                    
<a href="https://www.cnblogs.com/jmx-bigdata/archive/2018/11.html" rel="" target="">
    2018年11月(14)
</a>
 

                </li>                
                <li data-category-list-item-visible="true" style="display: block">
                    
<a href="https://www.cnblogs.com/jmx-bigdata/archive/2018/10.html" rel="" target="">
    2018年10月(9)
</a>
 

                </li>                
            
        </ul>


    </div>    
</div>

<!-- 最新评论 -->
<div id="sidebar_recentcomments" class="sidebar-block"><div class="catListComment">
<h3 class="catListTitle">最新评论</h3>

	<div class="RecentCommentBlock">
        <ul>
                    <li class="recent_comment_title"><a href="https://www.cnblogs.com/jmx-bigdata/p/14238167.html">1. Re:Flink on Hive构建流批一体数仓</a></li>
                    <li class="recent_comment_body"><p>很有参考价值, 解决了我很久的疑惑的, 因为没有用checkponit</p>
</li>
                    <li class="recent_comment_author">--cyl2007</li>
                    <li class="recent_comment_title"><a href="https://www.cnblogs.com/jmx-bigdata/p/13708832.html">2. Re:实时数仓|基于Flink1.11的SQL构建实时数仓探索实践</a></li>
                    <li class="recent_comment_body"><p>我在想，直接基于原始的mysql计算是不是也可以</p>
</li>
                    <li class="recent_comment_author">--huangrunxing</li>
                    <li class="recent_comment_title"><a href="https://www.cnblogs.com/jmx-bigdata/p/13708842.html">3. Re:Flink1.11中的CDC Connectors操作实践</a></li>
                    <li class="recent_comment_body"><p>没有流程图啊，感觉有点乱．．．．</p>
</li>
                    <li class="recent_comment_author">--李华梅</li>
                    <li class="recent_comment_title"><a href="https://www.cnblogs.com/jmx-bigdata/p/13708842.html">4. Re:Flink1.11中的CDC Connectors操作实践</a></li>
                    <li class="recent_comment_body"><p>牛鼻啊．．．</p>
</li>
                    <li class="recent_comment_author">--李华梅</li>
                    <li class="recent_comment_title"><a href="https://www.cnblogs.com/jmx-bigdata/p/13708914.html">5. Re:基于Hive的大数据实战项目</a></li>
                    <li class="recent_comment_body"><p>你好，这个网盘失效了 您能再发一遍吗？</p>
</li>
                    <li class="recent_comment_author">--wnagzhuang</li>
        </ul>
    </div>
</div>

</div>


<!-- 阅读排行榜 -->
<div id="sidebar_topviewedposts" class="sidebar-block"><div class="catListView">
<h3 class="catListTitle">阅读排行榜</h3>
	<div id="TopViewPostsBlock">
        <ul style="word-break:break-all">
                    <li>
                        <a href="https://www.cnblogs.com/jmx-bigdata/p/14021181.html">
                            1. 篇五|ClickHouse数据导入(Flink、Spark、Kafka、MySQL、Hive)(2335)
                        </a>
                    </li>
                    <li>
                        <a href="https://www.cnblogs.com/jmx-bigdata/p/13458131.html">
                            2. 实时数仓|Flink SQL之维表join
                                                                                                    (2176)
                        </a>
                    </li>
                    <li>
                        <a href="https://www.cnblogs.com/jmx-bigdata/p/13708834.html">
                            3. 篇二|什么是ClickHouse的表引擎?(1951)
                        </a>
                    </li>
                    <li>
                        <a href="https://www.cnblogs.com/jmx-bigdata/p/13708845.html">
                            4. 实时数仓|Flink SQL之维表join(1664)
                        </a>
                    </li>
                    <li>
                        <a href="https://www.cnblogs.com/jmx-bigdata/p/13458132.html">
                            5. 数据分析|使用多元线性回归构建销售额预测模型
                                                                                                    (1317)
                        </a>
                    </li>
        </ul>
    </div>
</div>

</div>

<!-- 评论排行榜 -->
<div id="sidebar_topcommentedposts" class="sidebar-block"><div class="catListFeedback">
<h3 class="catListTitle">评论排行榜</h3>
	<div id="TopFeedbackPostsBlock">
        <ul style="word-break:break-all">
                    <li>
                        <a href="https://www.cnblogs.com/jmx-bigdata/p/13708842.html">
                            1. Flink1.11中的CDC Connectors操作实践(2)
                        </a>
                    </li>
                    <li>
                        <a href="https://www.cnblogs.com/jmx-bigdata/p/14238167.html">
                            2. Flink on Hive构建流批一体数仓(1)
                        </a>
                    </li>
                    <li>
                        <a href="https://www.cnblogs.com/jmx-bigdata/p/13708832.html">
                            3. 实时数仓|基于Flink1.11的SQL构建实时数仓探索实践(1)
                        </a>
                    </li>
                    <li>
                        <a href="https://www.cnblogs.com/jmx-bigdata/p/13708914.html">
                            4. 基于Hive的大数据实战项目(1)
                        </a>
                    </li>
        </ul>
    </div>
</div>

</div>

<!-- 推荐排行榜 -->
<div id="sidebar_topdiggedposts" class="sidebar-block">
<div id="topdigg_posts_wrap">
    <div class="catListView">
        <h3 class="catListTitle">推荐排行榜</h3>
        <div id="TopDiggPostsBlock">
            <ul style="word-break: break-all">
                        <li>
                            <a href="https://www.cnblogs.com/jmx-bigdata/p/13458117.html">
                                1. Flink的时间与watermarks详解
                                                                                                    (3)
                            </a>
                        </li>
                        <li>
                            <a href="https://www.cnblogs.com/jmx-bigdata/p/14021181.html">
                                2. 篇五|ClickHouse数据导入(Flink、Spark、Kafka、MySQL、Hive)(2)
                            </a>
                        </li>
                        <li>
                            <a href="https://www.cnblogs.com/jmx-bigdata/p/13708832.html">
                                3. 实时数仓|基于Flink1.11的SQL构建实时数仓探索实践(1)
                            </a>
                        </li>
                        <li>
                            <a href="https://www.cnblogs.com/jmx-bigdata/p/13708842.html">
                                4. Flink1.11中的CDC Connectors操作实践(1)
                            </a>
                        </li>
                        <li>
                            <a href="https://www.cnblogs.com/jmx-bigdata/p/13708845.html">
                                5. 实时数仓|Flink SQL之维表join(1)
                            </a>
                        </li>
            </ul>
        </div>
    </div>
</div></div></div>
                    <script>loadBlogSideColumn();</script><iframe src="./篇二_什么是ClickHouse的表引擎_ - 大数据技术与数仓 - 博客园_files/container.html" style="visibility: hidden; display: none;"></iframe>
			</div>			
		</div><!--end: sideBarMain -->
	</div><!--end: sideBar 侧边栏容器 -->
	<div class="clear"></div>
	</div><!--end: main -->
	<div class="clear"></div>
	<div id="footer">
		<!--done-->
Copyright © 2021 大数据技术与数仓
<br><span id="poweredby">Powered by .NET 5.0 on Kubernetes</span>



	</div><!--end: footer -->
</div><!--end: home 自定义的最大容器 -->


    

    <input type="hidden" id="antiforgery_token" value="CfDJ8L-rpLgFVEJMgssCVvNUAjvaH17cgS4Wog78vXtt8EuSObtgnDGhNEj8BexYh44dmrFEvgeGrKCvkGF5aCLl91lF9lNAPW39zigwJj9k0Tljc_uiCUKioW8bcH_SQ1k-nL5e1Rj7h4Ro3o0jjGycZOk">


<iframe id="google_osd_static_frame_5002862439038" name="google_osd_static_frame" style="display: none; width: 0px; height: 0px;" src="./篇二_什么是ClickHouse的表引擎_ - 大数据技术与数仓 - 博客园_files/saved_resource(2).html"></iframe><iframe src="./篇二_什么是ClickHouse的表引擎_ - 大数据技术与数仓 - 博客园_files/aframe.html" width="0" height="0" style="display: none;"></iframe></body></html>